Slow query with DISTINCT
|Reported by:||Wraith4||Owned by:|
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 :)