Opened 14 years ago
Closed 12 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)
Note: See
TracTickets for help on using
tickets.
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 );