[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 (bug)                                    |       Status:  new      
 Priority:  high                                            |    Milestone:  2.8      
Component:  Optimization                                    |      Version:  2.7      
 Severity:  major                                           |   Resolution:           
 Keywords:  database, optimization, slow queries, filesort  |  
------------------------------------------------------------+---------------
Comment (by dbuser123):

 Another slow query (0.20s for a 30 MB comments table) I regularly
 encounter is this one:
 {{{
 SELECT comment_post_ID
 FROM comments
 WHERE LCASE(comment_author_email) = 'user at host.com' AND
 comment_subscribe='Y' AND comment_approved = '1'
 GROUP BY comment_post_ID
 }}}
 It would be better to always store e-mail as lowercase, stop using the
 LCASE function, and add an index on (comment_author_email,
 comment_post_ID).


 Furthermore I agree with rawalex. Using SQL_CALC_FOUND_ROWS and/or LIMIT
 high_number,25 both are performance killers. With 15.000 posts and a
 modern server, this query that shows the posts on an old page, takes 0.25s
 to complete. This is due to both the SQL_CALC_FOUND_ROWS and the LIMIT-
 clause. In an ideal situation, each post gets assigned a number that
 increases by one for every post and has an index on it. Then you can use
 SELECT max(number) instead of the SQL_CALC_FOUND_ROWS and use WHERE number
 BETWEEN max-5,max ORDER BY number DESC to circumvent the LIMIT-clause.
 This works on any page with posts, and should make even old pages very
 performant. It might be very time consuming to implement, but it's the
 best way to increase performance for large websites.
 {{{
 SELECT SQL_CALC_FOUND_ROWS posts.*
 FROM posts
 WHERE 1=1 AND posts.ID NOT IN (
     SELECT tr.object_id
     FROM term_relationships AS tr
     INNER JOIN term_taxonomy AS tt
     ON tr.term_taxonomy_id = tt.term_taxonomy_id
     WHERE tt.taxonomy = 'category' AND tt.term_id IN ('1924', '3428') )
   AND posts.post_type = 'post'
   AND (posts.post_status = 'publish')
 ORDER BY posts.post_date DESC
 LIMIT 2500, 5
 }}}

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