Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have a WordPress site with more than 8000 posts and everytime I add a new one the site becomes unresponsive. I checked the MySQL slow queries log and found out that it is performing a select that returns most of the rows in the posts table and is taking a lot of time to execute.

This is an example:

Query_time: 149.702704  
Lock_time: 0.000078  
Rows_sent: 4699  
Rows_examined: 9398  
Rows_affected: 0  
Rows_read: 4699
use 488726_wp;

SELECT `ID`, `post_author`, `post_date`, `post_date_gmt`, `post_status`, `post_name`, `post_modified`, `post_modified_gmt`, `post_parent`, `post_type`
    FROM `wp_posts` 
        WHERE ( (post_status = 'publish' AND (post_type = 'post' OR post_type = ''))  
            OR  (post_status = 'publish' AND post_type = 'page') )  
        AND post_password='' 
        ORDER BY post_modified DESC;

How can I find the source of these queries?

share|improve this question
add comment

3 Answers

Try using this plugin http://wordpress.org/extend/plugins/debug-queries/ for checking performance of your database queries. It shows lots of detatils about each and every query made and time the query needed to complete and the time needed to make the whole page.

share|improve this answer
    
the plugin also show Call From that might make you understand from where it query is done. –  Sisir Aug 17 '11 at 18:57
add comment

What you may want to do is perform an EXPLAIN on the query like this:

EXPLAIN SELECT ID, post_author, post_date,
post_date_gmt, post_status, post_name,
post_modified, post_modified_gmt, post_parent,
post_type FROM wp_posts
WHERE ( (post_status = 'publish' AND (post_type = 'post' OR post_type = ''))
OR (post_status = 'publish' AND post_type = 'page') )
AND post_password='' ORDER BY post_modified DESC;

This will reveal the access pattern taken by MySQL is gathering the data you need.

However, just staring at the WHERE and ORDER BY clauses, I would like to make thie following suggestion: create an index that can help the query speed up. Since post_status and post_type have static values in the query and post_modified present a sort order for those two columns, try this index please:

ALTER TABLE wp_posts ADD INDEX (post_status,post_type,post_modified);

Give it a Try !!!

share|improve this answer
add comment

This problem is related with Google XML Sitemaps plugin; http://wordpress.org/support/topic/plugin-google-xml-sitemaps-performance-issues-on-large-wp_posts-tables

You can use "Enable manual sitemap building via GET Request" option. And trigger the generation progress manually.

share|improve this answer
    
From where do you know that the OP is using that plugin? –  kaiser Oct 2 '13 at 20:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.