Reusing covering index for fields in FROM, from another query's index For the following query I have a covering index as so:

SELECT bar_id FROM foo WHERE user_id=:user_id AND date_sent=:date_sent;

ALTER TABLE foo ADD INDEX (user_id, date_sent, bar_id);


Now I have another query that I would like to add a covering index for:

SELECT user_id FROM foo WHERE bar_id=:bar_id AND date_sent=:date_sent;


Does the previous covering index also cover this new query? When I run EXPLAIN for the new query the following output is returned:

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+
|  1 | SIMPLE      | foo   | ref  | bar_id        | bar_id | 4       | const |    5 | NULL  |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+


I see that the key mentioned is bar_id, but that seems to me to show that this is not a covering index. Furthermore, the Extra field does not show the expected Using index output that would assure me that a covering index is in use.
The index IS covering for the second query. However, it is not USEFUL to support the seek. Because neither bar_id nor date_sent is leading the index, the optimiser cannot seek for it.

What you have achieved with the covering index is to make table scan faster for the second query. But you have not supported a better seek strategy.

This covering index would help both queries:

ALTER TABLE foo ADD INDEX (date_sent, user_id, bar_id)


Or if you prioritise the second query:

ALTER TABLE foo ADD INDEX (date_sent, bar_id, user_id)