[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):

 I'm sorry, nl.wordpress.org gave me an old version. The post_parent is
 fixed indeed, and this version only needs 23 queries.

 One comment on my initial report: instead of the UNION, post_status could
 be left out of the query. That way, the index could still be used for
 sorting.

 There are two new 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 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:3>
WordPress Trac <http://trac.wordpress.org/>
WordPress blogging software