WordPress.org

Make WordPress Core

Opened 5 years ago

Closed 3 years ago

#11336 closed defect (bug) (worksforme)

type_status_date index useless?

Reported by: Denis-de-Bernardy Owned by:
Milestone: Priority: normal
Severity: normal Version: 2.8.5
Component: Performance Keywords:
Focuses: Cc:

Description

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')) |
+----+-------------+-----------+------+-----------------------------------------+------------+---------+-------------+------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------+

Change History (3)

comment:1 @Denis-de-Bernardy5 years ago

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 );

comment:2 @dd325 years ago

  • Milestone changed from 3.0 to Future Release

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

No patch, moving to Future Release.

comment:3 @nacin3 years ago

  • Milestone Future Release deleted
  • Resolution set to worksforme
  • Status changed from new to closed

No new information, closing this one out.

Note: See TracTickets for help on using tickets.