Satisfying MySQL condition using Index

Question

Please consider following schema

CREATE table articles (
    id Int UNSIGNED NOT NULL AUTO_INCREMENT,
    cat_id Int UNSIGNED NOT NULL,
    status Int UNSIGNED  NOT NULL,
    date_added Datetime,
    Primary Key (id)) ENGINE = InnoDB;

CREATE INDEX cat_list_INX ON articles (cat_id, status, date_added);
CREATE INDEX categories_list_INX ON articles (cat_id, status);

I have written following two queries which can be completely satisfied by the above two indicies but MySQL is putting where in extra column.

mysql> EXPLAIN SELECT cat_id FROM articles USE INDEX (cat_list_INX) WHERE cat_id=158 AND status=2 ORDER BY date_added DESC LIMIT 500, 5;
+----+-------------+----------+------+---------------+--------------+---------+-------------+-------+--------------------------+
| id | select_type | table  ref         |   | type | possible_keys | key          | key_len | rows  | Extra                    |
+----+-------------+----------+------+---------------+--------------+---------+-------------+-------+--------------------------+
|  1 | SIMPLE      | articles | ref  | cat_list_INX  | cat_list_INX | 5       | const,const | 50698 | Using where; Using index |
+----+-------------+----------+------+---------------+--------------+---------+-------------+-------+--------------------------+


mysql> EXPLAIN SELECT cat_id FROM articles USE INDEX (categories_list_INX) WHERE cat_id=158 AND status=2;
+----+-------------+----------+------+---------------------+---------------------+---------+-------------+-------+--------------------------+
| id | select_type | tab key                 |le    | type | possible_keys       | key_len | ref         | rows  | Extra                    |
+----+-------------+----------+------+---------------------+---------------------+---------+-------------+-------+--------------------------+
|  1 | SIMPLE      | articles | ref  | categories_list_INX | categories_list_INX | 5       | const,const | 52710 | Using where; Using index |
+----+-------------+----------+------+---------------------+---------------------+---------+-------------+-------+--------------------------+

As far as I know where requires an additional disk seek. Why it's not just using index?

Answer 1

The first query is filtering records at the mysql level outside of the storage engine because of your "ORDER BY" clause using date_added field.

This can be mitigated by moving the date_added field first in the index like this

CREATE INDEX cat_list_INX ON articles (date_added, cat_id, status);

The 2nd query - my version of mysql is not showing a "Using where" - I would not expect to either - maybe its because I have no records.

mysql> EXPLAIN SELECT cat_id FROM articles USE INDEX (categories_list_INX) WHERE cat_id=158 AND status=2;
+----+-------------+----------+------+---------------------+---------------------+---------+-------------+------+-------------+
| id | select_type | table    | type | possible_keys       | key                 | key_len | ref         | rows | Extra       |
+----+-------------+----------+------+---------------------+---------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | articles | ref  | categories_list_INX | categories_list_INX | 8       | const,const |    1 | Using index |
+----+-------------+----------+------+---------------------+---------------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

Extra column info from High Performance MySQL:

Using Index: This indicates that MySQL will use a covering index to avoid accessing the table. Don't confuse covering indexes with the index access type.

Using Where: This means the MySQL server will post-filter rows after the storage engine retrieves them. Many WHERE conditions that involve columns in an index can be checked by the storage engine when (and if) it reads the index, so not all queries with a WHERE clause will show "Using where". Sometimes the presence of "Using where" is a hint that the query can benefit from different indexing.