Two (almost) identical queries, but only one of them uses the index

Question

I have a table with two columns, batch_id and study_id. There are indexes on both columns. I want to get a random row back from MySQL.

I originally wrote:

SELECT * FROM alt_join_batch_study ORDER BY RAND() LIMIT 1

but that proved slow. Here's the explain.

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    alt_join_batch_study    ALL    NULL    NULL    NULL    NULL    172235    Using temporary; Using filesort

I was surprised it wasn't using any indexes. I tried this:

SELECT batch_id, study_id FROM alt_join_batch_study ORDER BY RAND() LIMIT 1

and Lo! it was faster. And the explain shows the use of the index.

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    alt_join_batch_study    index    NULL    study_id    4    NULL    172235    Using index; Using temporary; Using filesort

Why wouldn't MySQL find the index when I do SELECT * ...?

Answer 1

If the table is InnoDB, here is what's happening:

The query optimizer sees SELECT * and does this

• Sees all columns are included in the SELECT list
• Uses the clustered index since all columns are included

The query optimizer sees SELECT batch_id, study_id and does this

• Sees SELECT list has two columns, not all columns
• Sees the study_id index (and other indexes)
• Chooses the study_id index
• Retrieves batch_id using PRIMARY KEY attached to study_id index entry

Why would this happen at all? The Query Optimizer is kinda picky

• Aug 13, 2013 : Why does MySQL ignore the index even on force for this order by?
• Nov 13, 2012 : Must an index cover all selected columns for it to be used for ORDER BY?

Sometimes, it can go the other way

• Apr 23, 2012 : Why is a secondary index chosen over a clustered index for SELECT COUNT(*) ...?

The key to your question is that the Query Optimizer does not view the two queries as almost identical. To the query optimizer, they are completely different in appearance and in its treatment.

Answer 2

It will run through all rows. However, it might be able to run through the "rows" in some index rather than running through the entire dataset.

See my blog with 8 techniques for speeding up ORDER BY RAND()

Your EXPLAIN may lead to a 9th. :)