SELECT SQL_CALC_FOUND_ROWS distinct wp

Discussion:

SELECT SQL_CALC_FOUND_ROWS distinct wp_posts query

Jeremi Bergman

2009-10-16 16:44:41 UTC

It seems there's this one query,
SELECT SQL_CALC_FOUND_ROWS distinct wp_posts.* FROM wp_posts LEFT JOIN
wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id =
wp_term_relationships.term_taxonomy_id WHERE 1=1 AND wp_posts.post_type =
'post' AND (wp_posts.post_status = 'publish') AND NOT EXISTS (SELECT * FROM
wp_term_relationships JOIN wp_term_taxonomy ON
wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
WHERE wp_term_relationships.object_id = wp_posts.ID AND
wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN (89)
) ORDER BY wp_posts.post_date DESC LIMIT 0, 50;

That is locking up my database and bringing down my site, at least once a
day. When I look in the slow queries log, it's full of them.

# Time: 091016 8:56:50
# ***@Host: mrsec_wp[mrsec_wp] @ localhost []
# Query_time: 13 Lock_time: 0 Rows_sent: 50 Rows_examined: 185675
use mrsec_wp;
SELECT SQL_CALC_FOUND_ROWS distinct wp_posts.* FROM wp_posts LEFT JOIN
wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id =
wp_term_relationships.term_taxonomy_id WHERE 1=1 AND wp_posts.post_type =
'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status =
'private') AND NOT EXISTS (SELECT * FROM wp_term_relationships JOIN
wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id =
wp_term_relationships.term_taxonomy_id WHERE wp_term_relationships.object_id
= wp_posts.ID AND wp_term_taxonomy.taxonomy = 'category' AND
wp_term_taxonomy.term_id IN (89) ) ORDER BY wp_posts.post_date DESC LIMIT 0,
50;

Any thoughts on how I can optimize this query? It takes approx 13 seconds
to execute this query. I have 28k records.

Thanks

--
Jeremi Bergman
865-622-7134

Jerry Johnson

2009-10-16 16:54:54 UTC

Permalink

Jeremi,

We are having the same problem.

One of our senior devs did this, which has helped greatly:

http://core.trac.wordpress.org/ticket/10964

Jerry Johnson
Senior Application Developer
*Dolan Media Company* (NYSE:DM)
612.659.7363 :: 612.718.8781 (m)
jerry.johnson [at] dolanmedia [dot] com

Post by Jeremi Bergman
It seems there's this one query,
SELECT SQL_CALC_FOUND_ROWS distinct wp_posts.* FROM wp_posts LEFT JOIN
wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
<snip>
Any thoughts on how I can optimize this query? It takes approx 13 seconds
to execute this query. I have 28k records.

Otto

2009-10-16 17:35:49 UTC

Permalink

That doesn't appear to be a core query. At least, not in the latest
trunk (no idea on 2.8.4). The word "EXISTS" is not used in that sort
of context anywhere in the code that I can see.

Got any weird plugins?

-Otto
Sent from Memphis, TN, United States

Post by Jeremi Bergman
It seems there's this one query,
SELECT SQL_CALC_FOUND_ROWS distinct wp_posts.* FROM wp_posts LEFT JOIN
wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id =
wp_term_relationships.term_taxonomy_id WHERE 1=1 AND wp_posts.post_type =
'post' AND (wp_posts.post_status = 'publish') AND NOT EXISTS (SELECT * FROM
wp_term_relationships JOIN wp_term_taxonomy ON
wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
WHERE wp_term_relationships.object_id = wp_posts.ID AND
wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN (89)
) ORDER BY wp_posts.post_date DESC LIMIT 0, 50;
That is locking up my database and bringing down my site, at least once a
day. When I look in the slow queries log, it's full of them.
# Time: 091016 8:56:50
# Query_time: 13 Lock_time: 0 Rows_sent: 50 Rows_examined: 185675
use mrsec_wp;
SELECT SQL_CALC_FOUND_ROWS distinct wp_posts.* FROM wp_posts LEFT JOIN
wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id =
wp_term_relationships.term_taxonomy_id WHERE 1=1 AND wp_posts.post_type =
'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status =
'private') AND NOT EXISTS (SELECT * FROM wp_term_relationships JOIN
wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id =
wp_term_relationships.term_taxonomy_id WHERE wp_term_relationships.object_id
= wp_posts.ID AND wp_term_taxonomy.taxonomy = 'category' AND
wp_term_taxonomy.term_id IN (89) ) ORDER BY wp_posts.post_date DESC LIMIT 0,
50;
Any thoughts on how I can optimize this query? It takes approx 13 seconds
to execute this query. I have 28k records.
Thanks
--
Jeremi Bergman
865-622-7134
_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

Jeremi Bergman

2009-10-16 18:56:47 UTC

Permalink

Actually, I believe you are right. I narrowed it down to the *Advanced
Category Excluder* plugin. I've disabled it, and am watching the slow
query logs.

Thanks

Post by Otto
That doesn't appear to be a core query. At least, not in the latest
trunk (no idea on 2.8.4). The word "EXISTS" is not used in that sort
of context anywhere in the code that I can see.
Got any weird plugins?
-Otto
Sent from Memphis, TN, United States

FROM

Post by Jeremi Bergman
wp_term_relationships JOIN wp_term_taxonomy ON
wp_term_taxonomy.term_taxonomy_id =

wp_term_relationships.term_taxonomy_id

Post by Jeremi Bergman
WHERE wp_term_relationships.object_id = wp_posts.ID AND
wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN

(89)

Post by Jeremi Bergman
) ORDER BY wp_posts.post_date DESC LIMIT 0, 50;
That is locking up my database and bringing down my site, at least once a
day. When I look in the slow queries log, it's full of them.
# Time: 091016 8:56:50
# Query_time: 13 Lock_time: 0 Rows_sent: 50 Rows_examined: 185675
use mrsec_wp;
SELECT SQL_CALC_FOUND_ROWS distinct wp_posts.* FROM wp_posts LEFT JOIN
wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id =
wp_term_relationships.term_taxonomy_id WHERE 1=1 AND wp_posts.post_type =
'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status =
'private') AND NOT EXISTS (SELECT * FROM wp_term_relationships JOIN
wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id =
wp_term_relationships.term_taxonomy_id WHERE

wp_term_relationships.object_id

Post by Jeremi Bergman
= wp_posts.ID AND wp_term_taxonomy.taxonomy = 'category' AND
wp_term_taxonomy.term_id IN (89) ) ORDER BY wp_posts.post_date DESC LIMIT

Post by Jeremi Bergman
50;
Any thoughts on how I can optimize this query? It takes approx 13

seconds

Post by Jeremi Bergman
to execute this query. I have 28k records.
Thanks
--
Jeremi Bergman
865-622-7134
_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

--
Jeremi Bergman
865-622-7134

Otto

2009-10-16 19:24:18 UTC

Permalink

Sounds like that plugin needs an update.

The category__not_in parameter works just fine for query_posts, WP_Query, etc.

-Otto
Sent from Memphis, TN, United States

Actually, I believe you are right. I narrowed it down to the *Advanced
Category Excluder* plugin. I've disabled it, and am watching the slow
query logs.
Thanks

FROM

Post by Jeremi Bergman
wp_term_relationships JOIN wp_term_taxonomy ON
wp_term_taxonomy.term_taxonomy_id =

wp_term_relationships.term_taxonomy_id

Post by Jeremi Bergman
WHERE wp_term_relationships.object_id = wp_posts.ID AND
wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN

(89)

wp_term_relationships.object_id

Post by Jeremi Bergman
= wp_posts.ID AND wp_term_taxonomy.taxonomy = 'category' AND
wp_term_taxonomy.term_id IN (89) ) ORDER BY wp_posts.post_date DESC LIMIT

Post by Jeremi Bergman
50;
Any thoughts on how I can optimize this query? It takes approx 13

seconds

_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

--
Jeremi Bergman
865-622-7134
_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

William Canino

2009-10-16 21:20:16 UTC

Permalink

Post by Otto
Sounds like that plugin needs an update.
The category__not_in parameter works just fine for query_posts, WP_Query, etc.

I will agree with Otto, but Jeremi you may need to consider overall load

This is the SQL you gave:

SELECT SQL_CALC_FOUND_ROWS distinct wp_posts.* FROM wp_posts LEFT JOIN
wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id =
wp_term_relationships.term_taxonomy_id WHERE 1=1 AND wp_posts.post_type =
'post' AND (wp_posts.post_status = 'publish') AND NOT EXISTS (SELECT * FROM
wp_term_relationships JOIN wp_term_taxonomy ON
wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
WHERE wp_term_relationships.object_id = wp_posts.ID AND
wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN (89)
) ORDER BY wp_posts.post_date DESC LIMIT 0, 50;

but the following are the SQL generated by
query_posts(array('category__not_in' => array(86), 'posts_per_page' =>
50));

SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND
wp_posts.ID NOT IN ( SELECT tr.object_id FROM wp_term_relationships AS
tr INNER JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id =
tt.term_taxonomy_id WHERE tt.taxonomy = 'category' AND tt.term_id IN
('86') ) 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, 50

SELECT FOUND_ROWS()

SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN
wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN
wp_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 (<fifty post ids>) ORDER BY t.name ASC

SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN
(<fifty post ids>)

whereas I suspect you only need the first one.

Good luck!

W

Post by Otto
Sounds like that plugin needs an update.
The category__not_in parameter works just fine for query_posts, WP_Query, etc.
-Otto
Sent from Memphis, TN, United States

Actually, I believe you are right. I narrowed it down to the *Advanced
Category Excluder* plugin. I've disabled it, and am watching the slow
query logs.
Thanks

FROM

Post by Jeremi Bergman
wp_term_relationships JOIN wp_term_taxonomy ON
wp_term_taxonomy.term_taxonomy_id =

wp_term_relationships.term_taxonomy_id

Post by Jeremi Bergman
WHERE wp_term_relationships.object_id = wp_posts.ID AND
wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN

(89)

wp_term_relationships.object_id

Post by Jeremi Bergman
= wp_posts.ID AND wp_term_taxonomy.taxonomy = 'category' AND
wp_term_taxonomy.term_id IN (89) ) ORDER BY wp_posts.post_date DESC LIMIT

Post by Jeremi Bergman
50;
Any thoughts on how I can optimize this query? It takes approx 13

seconds

_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

--
Jeremi Bergman
865-622-7134
_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

Aaron D. Campbell

2009-10-17 05:52:06 UTC

Permalink

Just as a heads up to anyone else. That plugin can be really "handy"
but it creates extremely heavy-handed queries, especially when combined
with anything else. Again, it's nice, but it doesn't scale well. I'm
hoping to get some time to see if I can clean it up and either pass the
fixes back to the author or fork the plugin.

Post by Jeremi Bergman
Actually, I believe you are right. I narrowed it down to the *Advanced
Category Excluder* plugin. I've disabled it, and am watching the slow
query logs.
Thanks

Jeremi Bergman

2009-10-17 14:10:46 UTC

Permalink

Yes, it excludes certain categories from the RSS feed, among other things,
which is what we're looking for?

Is there an alternative to excluding categories from the rss feeds?

Just as a heads up to anyone else. That plugin can be really "handy" but
it creates extremely heavy-handed queries, especially when combined with
anything else. Again, it's nice, but it doesn't scale well. I'm hoping to
get some time to see if I can clean it up and either pass the fixes back to
the author or fork the plugin.

Post by Jeremi Bergman
Actually, I believe you are right. I narrowed it down to the *Advanced
Category Excluder* plugin. I've disabled it, and am watching the slow
query logs.
Thanks

_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

--
Jeremi Bergman
865-622-7134

Paul

2009-10-17 14:57:00 UTC

Permalink

Not to comment on the plugin author's work. But to exclude categories
from the RSS feed is quite simple and can be accomplished using build-
in WP hooks and not any query manipulation needed. Consider the simple
function below.

In this I want to exclude categories 1, 32, 16 and 28 from the main
RSS feed. Drop this into your theme's functions.php and tweak the
category IDs.

function myRSSPostsFilter($query) {
if ($query->is_feed) {
$query->set('cat','-1,-32,-16,-28');
}
return $query;
}
add_filter('pre_get_posts','myRSSPostsFilter');

Or you can use my Simply Exclude plugin http://wordpress.org/extend/plugins/simply-exclude/
to have a nice user interface so you don't have to touch the code.
The code uses the same basic principal as the function above. I wrote
a similar plugin that allow exclude of categories, tags and authors
from is_front, is_archive, is_search and is_feed actions.

Best,

Paul

Post by Jeremi Bergman
Yes, it excludes certain categories from the RSS feed, among other things,
which is what we're looking for?
Is there an alternative to excluding categories from the rss feeds?
--
Jeremi Bergman
865-622-7134
_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

Jeremi Bergman

2009-10-17 17:39:51 UTC

Permalink

Thanks Paul. That function will work perfectly without the overhead of a
plugin.

Not to comment on the plugin author's work. But to exclude categories from
the RSS feed is quite simple and can be accomplished using build-in WP hooks
and not any query manipulation needed. Consider the simple function below.
In this I want to exclude categories 1, 32, 16 and 28 from the main RSS
feed. Drop this into your theme's functions.php and tweak the category IDs.
function myRSSPostsFilter($query) {
if ($query->is_feed) {
$query->set('cat','-1,-32,-16,-28');
}
return $query;
}
add_filter('pre_get_posts','myRSSPostsFilter');
Or you can use my Simply Exclude plugin
http://wordpress.org/extend/plugins/simply-exclude/ to have a nice user
interface so you don't have to touch the code. The code uses the same basic
principal as the function above. I wrote a similar plugin that allow exclude
of categories, tags and authors from is_front, is_archive, is_search and
is_feed actions.
Best,
Paul
Yes, it excludes certain categories from the RSS feed, among other things,

Post by Jeremi Bergman
which is what we're looking for?
Is there an alternative to excluding categories from the rss feeds?
--
Jeremi Bergman
865-622-7134
_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

_______________________________________________
wp-hackers mailing list
http://lists.automattic.com/mailman/listinfo/wp-hackers

--
Jeremi Bergman
865-622-7134