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.