type_status_date index useless?

[Denis-de-Bernardy]

This is for the front page of a live 2.8.6 site:

mysql> explain extended SELECT SQL_CALC_FOUND_ROWS  www_posts.* FROM www_posts  WHERE 1=1  AND www_posts.post_type = 'post' AND www_posts.post_status = 'publish' ORDER BY www_posts.post_date DESC LIMIT 0, 10;
+----+-------------+-----------+------+------------------------------+-------------+---------+-------+------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table     | type | possible_keys                | key         | key_len | ref   | rows | filtered | Extra                                                                                                                                                         |
+----+-------------+-----------+------+------------------------------+-------------+---------+-------+------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | www_posts | ref  | post_status,type_status_date | post_status | 62      | const |   10 |   100.00 | Using where with pushed condition: ((`semiologic`.`www_posts`.`post_type` = 'post') and (`semiologic`.`www_posts`.`post_status` = 'publish')); Using filesort |
+----+-------------+-----------+------+------------------------------+-------------+---------+-------+------+----------+-------------------------------------------------------------------------------

mysql> create index denis_test on www_posts (post_status, post_type, post_date);

mysql> explain extended SELECT SQL_CALC_FOUND_ROWS  www_posts.* FROM www_posts  WHERE 1=1  AND www_posts.post_type = 'post' AND www_posts.post_status = 'publish' ORDER BY www_posts.post_date DESC LIMIT 0, 10;
+----+-------------+-----------+------+-----------------------------------------+------------+---------+-------------+------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table     | type | possible_keys                           | key        | key_len | ref         | rows | filtered | Extra                                                                                                                                         |
+----+-------------+-----------+------+-----------------------------------------+------------+---------+-------------+------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | www_posts | ref  | post_status,type_status_date,denis_test | denis_test | 124     | const,const |   10 |   100.00 | Using where with pushed condition: ((`semiologic`.`www_posts`.`post_type` = 'post') and (`semiologic`.`www_posts`.`post_status` = 'publish')) |
+----+-------------+-----------+------+-----------------------------------------+------------+---------+-------------+------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------+

[Denis-de-Bernardy]

btw, it doesn't use this one either:

( post_type, post_status, post_date );

but it does use this one:

( post_status, post_type, post_date );

[dd32]

Are the post_type related indicies used now that we've got more custom post_type support?

No patch, moving to Future Release.

[nacin]

No new information, closing this one out.