[wp-trac] [WordPress Trac] #7836: Slow query with DISTINCT

[WordPress Trac]

#7836: Slow query with DISTINCT
--------------------------+-------------------------------------------------
 Reporter:  Wraith4       |       Owner:  anonymous
     Type:  defect        |      Status:  new      
 Priority:  normal        |   Milestone:           
Component:  Optimization  |     Version:  2.6.1    
 Severity:  normal        |    Keywords:           
--------------------------+-------------------------------------------------
 Hello, I have a big Wordpress site, with > 5000 posts and > 70000
 relations between posts and taxonomy.

 There is a problem with queries like this one:
 SELECT DISTINCT p.ID, p.post_title, p.post_date, p.comment_count,
 ount(t_r.object_id) as cnt FROM wp_term_taxonomy t_t, p_term_relationships
 t_r, wp_posts p WHERE t_t.taxonomy ='post_tag' AND t_t.term_taxonomy_id =
 _r.term_taxonomy_id AND t_r.object_id = p.ID AND (t_t.term_id IN ('262',
 '246', '279', '619', '254', '438', '288', '257', '')) AND p.ID != 6581 AND
 p.post_status = 'publish' AND p.post_date_gmt < 2008-10-06 13:14:24' GROUP
 BY t_r.object_id ORDER BY cnt DESC, p.post_date_gmt DESC LIMIT 10;

 It generates huge CPU usage on my server, every query like this one takes
 aprox. 18secs. I have tried similar queries, but without "DISTINCT", or
 without  "DISTINCT" and with"GROUP BY t_r.object_id,p.ID" ...both
 generates the same result like the DISTINCT one, but in 0,2sec.

 Can I edit this query? Can I remove the distinct, or use GROUP BY? Where
 is the query defined?

 Thanks in advance :)

-- 
Ticket URL: <http://trac.wordpress.org/ticket/7836>
WordPress Trac <http://trac.wordpress.org/>
WordPress blogging software