Speeding Up Your Selects and Sorts

4 Mar 2010 — eric

When you are using a framework, they typically set your VARCHAR size automatically to 255. This is normally fine since you are letting the framework abstract you away from most of the SQL. But if you interact with your SQL, there is a way to get a decent speed increase on your SELECTs and ORDER BYs when you are working with VARCHARs.

The VARCHAR data type is only variable character size for storage, not for sorting and buffering. In fact, since the MySQL optimizer doesn’t know how big the data in that column can be, it has to allocate the maximum size possible for that column. So for sorting and buffering of the name and email columns below would take up 310 bytes per row.
Read the rest of this entry »

Posted in MySQL. Tags: , . Comments

Model Specific Formatted Search Results Using Thinking Sphinx

8 Feb 2010 — eric

Having recently implemented Thinking Sphinx on one of my web sites, I thought it would be cool to be able to search every indexed model. With Thinking Sphinx, it’s easy to have a bunch of different classes returned in the results. The tougher part is displaying them in a way that is organized (although admittedly not very DRY).
Read the rest of this entry »

Posted in Rails. Tags: , , . Comments

When To Use MySQL Cursor Classes In Python

18 Jan 2010 — eric

I have been writing a lot of code that has been interacting with MySQL lately. Sometimes I find it easier to work the result set in a dictionary form and other times it is easier with an array. But in order to not break all your code, it is necessary to set a default cursor class that keeps your code consistent. More often than not, I find using using a arrays is easier since I just want quick access to all the retrieved data. I also end up making my SELECT calls while specifying the columns and order of the columns I want returned.

The reason that using cursor classes is handy is because Python doesn’t come with a mysql_fetch_assoc like PHP or selectrow_hashref like Perl’s DBI interface. Python uses cursor dictionaries to bridge this gap. Ultimately your result is the same. But as with Perl and PHP, defaulting to cursor dictionaries isn’t a good idea for larger datasets because of the extra processing time and memory required to convert the data.
Read the rest of this entry »

Posted in python. Tags: , , . Comments

Migrations Without belongs_to Or references

14 Oct 2009 — eric

Normally when do a database migration in Rails, when adding ownership from a model to another model, you use the concept of belongs_to or references:

  create_table :comments do |t|
    t.belongs_to :user
    t.references :post
  end

Interestingly enough, these methods are only available during the initial table creation. If you want to add a reference to a model that is created later, you have to do it the old fashioned way, by just adding a column:

   add_column :comments, :group_id, :integer

Doing it this way is clean, easy, and definitely meets the KISS principle. But I do find it interesting that one can’t add an association later in the game. Sometimes the Rails way is just KISS and adding the column by hand.

Posted in Rails. Tags: . Comments