[wp-trac] Re: [WordPress Trac] #7415: "using filesort" in default install

[WordPress Trac]

#7415: "using filesort" in default install
------------------------------------------------------------+---------------
 Reporter:  dbuser123                                       |        Owner:  anonymous
     Type:  defect                                          |       Status:  new      
 Priority:  normal                                          |    Milestone:  2.7      
Component:  Optimization                                    |      Version:           
 Severity:  normal                                          |   Resolution:           
 Keywords:  database, optimization, slow queries, filesort  |  
------------------------------------------------------------+---------------
Comment (by dbuser123):

 '''Oh, there are some code-tags. Here's everything that still needs
 attention; everything mentioned above can be ignored.'''

 The slowest query by far (40% of total query time) is this one

 {{{SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND
 wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR
 wp_posts.post_status = 'private') ORDER BY wp_posts.post_date DESC LIMIT
 0, 6}}}

 This query had to sort 3000 rows BY HAND (only if logged in as admin).
 Because of the OR, the post_date part of the index
 (post_type,post_status,post_date,ID) could not be used for sorting. If you
 leave out the column post_status out of the index, the index can be used
 for sorting.

 Yet another one:
 {{{SELECT * FROM wp_posts WHERE (post_type = 'page' AND post_status =
 'publish') ORDER BY menu_order, post_title ASC}}} Has to be ordered
 manually again because there's no appropriate index.

 The second problem with this query is the SQL_CALC_FOUND_ROWS. It makes
 MySQL retrieve all rows, thus causing much disk i/o. It's way faster to do
 a second query that does a count(*) because it can be done by reading the
 index instead of the rows. This made the query 10 times as fast for me.

 There are two more queries that could be optimized by taking out the ORDER
 BY clause: {{{SELECT t.*, tt.*, tr.object_id FROM iphone_terms AS t INNER
 JOIN iphone_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN
 iphone_term_relationships AS tr ON tr.term_taxonomy_id =
 tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag') AND
 tr.object_id IN (9109, 9103, 9052, 9112, 9100, 9096) ORDER BY t.name
 ASC}}}
 {{{SELECT t.*, tt.* FROM iphone_terms AS t INNER JOIN iphone_term_taxonomy
 AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') ORDER BY
 t.name ASC}}}

 This query is also new and slow:
 {{{SELECT object_id, term_taxonomy_id FROM iphone_term_relationships INNER
 JOIN iphone_posts ON object_id = ID WHERE term_taxonomy_id IN
 (3,27,10,8,5,28,4,26,4286,13,39,41,3296,2169,2699,36,1920,35,2881,23,42,14,21,15,24,32,34,48,25,22,20,2158,17,977,47,18,31,37,40,16,2159,19,45,30,46,1182,33,3431,11,49,12,7,6,29,2539,43,38,4456,9,44,4138,1)
 AND post_type = 'post' AND post_status = 'publish'}}}
 On iphone_term_relationships it uses an index on
 (object_id,term_taxonomy_id), while an index on
 (term_taxonomy_id,object_id) would be way better. The reason why object_id
 is still in the index, is because then rows don't have to be read.

 And for this query, an index on tt.taxonomy would be nice:
 {{{SELECT t.*, tt.* FROM iphone_terms AS t INNER JOIN iphone_term_taxonomy
 AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('link_category') AND
 tt.count > 0 ORDER BY t.name ASC}}}

-- 
Ticket URL: <http://trac.wordpress.org/ticket/7415#comment:4>
WordPress Trac <http://trac.wordpress.org/>
WordPress blogging software