Using FORCE INDEX with Rails

Posted on April 04, 2010

Avoiding a full table scan when performing a MySQL query with both an ORDER and a WHERE clause is more difficult than you would think. This is especially true if you’re only selecting a subset of the table from a foreign key.

First off, you must add a compound index with the order column first. If the index is constructed with the column used to order the table last, the index will not work as expected. 

add_index :posts [:created_at, :blog_id]

Next, you have to specify the index in the ActiveRecord call with USE INDEX or FORCE INDEX or else MySQL will not use it by default.

named_scope :ordered, {
            :from => 'events FORCE INDEX (index_posts_on_created_at_and_blog_id)',
            :order => 'created_at DESC' }

The Rails add_index method names the MySQL index as index_table_name_on_column_1_at_and_column_2 by default. If you want to list the indexes available for your database, you can perform the query: SHOW INDEXES to investigate.

It’s possible to test the index by copying the MySQL queries from your Rails log and prefixing them with EXPLAIN or EXPLAIN EXTENDED. Using either of these will display the number of rows searched for each query. If your query is scanning more rows than you’ve selected, that is a clear sign that the index your using is either not being used or incorrect.

Trackbacks

Use this link to trackback from your own site.

Comments

Leave a response

  1. Edward Rudd Mon, 03 Oct 2011 17:52:24 UTC

    The reason why MySQL won’t use the index is because the index is sorted ASC, and you are requesting it be sorted DESC. For some reason rails lacks the ability to specify the sort direction when creating indexes.

    Creating an index thusly would solve your issue AND increase performance over what you are currently doing.

    CREATE INDEX posts_created_at_and_blog_id ON posts (create_at DESC, blog_id)

    though probably you want to do

    CREATE INDEX posts_blog_id_and_created_at ON posts (blog_id, created_at DESC)

    As I’m assuming your queries are like this.

    SELECT posts.* from posts where blog_id = 23 ORDER BY created_at DESC

  2. Tony Perrie Mon, 03 Oct 2011 18:08:13 UTC

    Thanks. I didn’t know that.

Comments