lundi 26 janvier 2015

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.





Aucun commentaire:

Enregistrer un commentaire