Performance of multi-column MySQL indexes when using only one column in a query

Question

I have a query on my database as such:

SELECT * FROM expenses WHERE user_id = ? AND dated_on = ?

I have added an index to the table on both the user_id and dated_on columns. When I inspect the indexes using SHOW INDEXES FROM expenses, there are two lines -- one with a seq_in_index value of 1, the other with a seq_in_index value of 2.

My question is, if I then submit a query which uses only one of the two WHERE clauses, e.g.:

SELECT * FROM expenses WHERE user_id = ?

Is there any benefit to creating another index which solely indexes the user_id column, or will the user_id/dated_on index described above be used just as efficiently?

Finally, how about if use the query:

SELECT * FROM expenses WHERE dated_on = ?

How does the seq_in_index value of 2 affect index use and performance in this situation?

Answer 1

If your index on user_id and dated_on is really in that order (user_id first), then it will be used for a user_id query also. You can check by using EXPLAIN to see the actual strategy for the query.

Answer 2

MySQL can use any left portion of an index.

In your example SELECT * FROM expenses WHERE user_id = ? will use the index but SELECT * FROM expenses WHERE dated_on = ? won't.

For a 3-column index A, B, C, WHERE A = ? AND B = ? will use an index over A and B, but WHERE A = ? AND C = ? will only use an index on A