Imaginemos un mundo libre

La paz interior comienza en el momento en el que decides no permitir, que ninguna persona o evento, tome el control de tus emociones.

Query analyzer for Rails

leave a comment »

I found an interesting and very useful plugin for rails called Query analyzer that allows us check for tables not optimized in our database. It really helps a lot to optimize tables and queries and put indexes on our conditions columns and primary/foreign keys. Sometimes, we forget to do things so simple like this.  Here is a great explanation of what the plugin does.

How to use it? Just install the plugin with:

script/plugin install http://svn.nfectio.us/plugins/query_analyzer

Then check your logs. You can see your console as well. Once you have the plugin installed you will see all queries your application does and how they are currently managed.

For example:

I have a table called form_help_divs. It is a table to show some messages in forms around my application. I usually make a call to the database to get a form_help_div record. The query generated is like this:

SELECT * FROM `form_help_divs` WHERE (`form_help_divs`.`name` = 'patch_vuln') LIMIT 1

So in theory. The query should check for only one row, but that is not true because the name column doesn’t have an index. And how I realized that?. I just checked my log and saw this:

Analyzing FormHelpDiv Load

select_type | key_len | type | Extra       | id | possible_keys | rows | table          | ref | key
---------------------------------------------------------------------------------------------------
SIMPLE      |         | ALL  | Using where | 1  |               | 33   | form_help_divs |     |

As you see the query is looking for all records (in this case 33) in the database searching for the name ‘patch_vuln’. That is not good guys. Let us optimize our queries by adding and index to that table. We can add it by hand in our mysql interface, but is better to create migrations.

class AddFormHelpDivIndexes < ActiveRecord::Migration
    def self.up
        add_index :form_help_divs, :name
    end

    def self.down
        remove_index :form_help_divs, :name
    end
end

Type rake db:migrate in your console and your migration will be executed.Now run your application again and you will see the difference in your logs and console. This is the result:

FormHelpDiv Load (0.001585)   SELECT * FROM `form_help_divs` WHERE (`form_help_divs`.`name` = 'patch_vuln') LIMIT 1

Analyzing FormHelpDiv Load

select_type | key_len | type | Extra     | id | possible_keys | rows |table  | ref   | key
-------------------------------------------------------------------------------------------------
SIMPLE   |768|ref|Using where|1|name,index_form_help_divs_on_name|1|form_help_divs | const | name

¡What a big difference!. Now the query is looking just for one row in the database and that will increase the query speed significantly.

There are other ways to make our applications running faster with rails. Adding indexes is one of those ways and let us gain a lot of optimization specially if we are working with big applications and tables with so many records.
Thanks for your visit to the blog. You can follow me on Twitter:

Written by Ronny Yabar

July 3, 2008 at 9:32 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: