Two (almost) identical queries, but only one of them uses the index 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 * ...?
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.