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?
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.
What you may want to do is perform an EXPLAIN on the query like this:
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:
Give it a Try !!!
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.
Try the following plugin, Query Interface, which will let you show the queries as well as give you an interface where you can also check that what the query is taking by checking its indexes or explanation.