Lots of SQL queries

Question

I have a site with more than 300,000 posts (but really little posts) and cheking why is going so slow I've seen that in the hompage it makes something like this:

"Generated in 15.024 seconds. Made 9492 queries to database and 0 cached queries. Memory used - 81.12MB"

Home only displays the first 10 psts and runs a couple of twitter plugins, any idea about how to reduce this massive amount of queries?

Thanks!

Answer 1

Delete post/page revisions and that will reduce queries as well as the overall size of the DB. Run this in phpmyadmin (backup the DB first):

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

Change table prefix above if needed. And run optimize from phpmyadmin afterward.

Add this to wp-config.php to prevent revisions from accumulating again:

define ('WP_POST_REVISIONS', 0);

See http://codex.wordpress.org/Editing_wp-config.php#Post_Revisions

And use that plugin pointed out above to see what else is querying the DB.

Answer 2

Two other ideas:

1. What Plugins are you using? Some Plugins are especially database-intensive, such as related-content type Plugins.

2. What is your permalink structure? If the site has several posts/pages, and is using %postname alone as the permalink structure, you could be invoking verbose rewrite rules, resulting in a LOT of db queries.