Using two single-column indexes in where and orderby clause

I have googled a lot and couldn’t find a clear answer to my question

assume we have this query

SELECT * WHERE user_id = x ORDER BY date_created

If we have a single column index on user_id and another one on date_created, does the optimizer use both indexes? or just user_id index?

Answer

This is your query:

SELECT *
FROM mytable
WHERE user_id = 123 
ORDER BY date_created

If you have two distinct indexes, then MySQL might use the index on user_id to apply the where predicate (if it believes that it will speed up the query, depending on the cardinality of your data, and other factor). It will not use the index on date_created, because it has no way to relate the intermediate resultset that satisfy the where predicate to that index.

For this query, you want a compound index on (user_id, date_created). The database uses the first key in the index to filter the dataset: in the index B-tree, matching rows are already sorted by date, so the order by operation becoms a no-op.

I notice that you are using select *; this is not a good practice in general, and not good for performance. If there are other columns in the table than the user and date, this forces to database to look up at the table to bring the corresponding rows after filtering and ordering through the index, which can be more expensive than not using the index at all. If you just need a few columns, then enumerate them:

SELECT date_created, first_name, last_name 
FROM mytable
WHERE user_id = 123 
ORDER BY date_created

And have an index on (user_id, date_created, first_name, last_name). That’s a covering index: the database can execute the whole query using on the index, without looking up the table itself.