MySQL multi-column index performance when using only one column in a query

Question

I have a query in my database as such:

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

I added an index to the user_id and dated_on column table. When I check indexes using SHOW INDEXES FROM expenses , there are two lines: one with seq_in_index value 1, the other with seq_in_index value 2.

My question is: if I then send a request that uses only one of the two WHERE clauses, for example:

 SELECT * FROM expenses WHERE user_id = ? 

Is there any use for creating another index that only indexes the user_id column, or will the user_id / dated_on index also known above be used?

Finally, how about using a query:

 SELECT * FROM expenses WHERE dated_on = ? 

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

Answer 1

MySQL can use any left side of the index .

In your example, SELECT * FROM expenses WHERE user_id = ? the index will be used, but SELECT * FROM expenses WHERE dated_on = ? will not.

For an index with three columns A, B, C, WHERE A = ? AND B = ? WHERE A = ? AND B = ? will use index over A and B, but WHERE A = ? AND C = ? WHERE A = ? AND C = ? will only use the index on A

Answer 2

If your index in user_id and date_on is indeed in that order (user_id first), it will also be used to query user_id. You can check with EXPLAIN to see the actual query strategy.