[wp-trac] [WordPress Trac] #11336: type_status_date index useless?

[WordPress Trac]

#11336: type_status_date index useless?
-------------------------------+--------------------------------------------
 Reporter:  Denis-de-Bernardy  |       Owner:       
     Type:  defect (bug)       |      Status:  new  
 Priority:  normal             |   Milestone:  3.0  
Component:  Performance        |     Version:  2.8.5
 Severity:  normal             |    Keywords:       
-------------------------------+--------------------------------------------
 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')) |
 +----+-------------+-----------+------+-----------------------------------------+------------+---------+-------------+------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------+

 }}}

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/11336>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software