Speeding up a (slow) huge wordpress database

1) Use mysqltuner on WAMP (you can't install on shared hosting without root) to tune MySQL and change query cache, memory, etc. Will make a huge difference on WAMP and the eventual live server. https://github.com/rackerhacker/MySQLTuner-perl

2) Be sure to delete post/page revisions from the database. WP can store many revisions that greatly impact DB speed. I've seen DB sizes drop 90% after deleting post/page revisions.

Run as SQL query in phpmyadmin to delete revisions; change table prefix as necessary:

DELETE a,b,cFROM wp_posts aLEFT 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'

Then optimize all tables. And then add

define ('WP_POST_REVISIONS', FALSE);

near the top of wp-config.php (somewhere after the opening <?php ... ) to disable future revisions.

3) Increase memory for php and WP for better performance:

Edit the memory_limit line in your php.ini to 128M:

memory_limit = 128M;

Or add this line at the top of your .htaccess file:

php_value memory_limit 128M

If that doesn't work or throws an error, add this line near the top of your wp-config.php file right after the opening <?php

define('WP_MEMORY_LIMIT', '128M');

4) On the final VPS, configure http.conf for performance and possibly use different box for the MySQL server itself.

Shared host obviously hit the limit of performance. Problem is insufficient hardware, not the queries themselves, so what you'll have to do is get a dedicated machine for yourself. Bottleneck here seems to be MySQL which is usually disk bound, but if your site is about to grow really large I'd start preparing somewhat different architecture with load balancers for HTTP and a powerful machine for MySQL (I think that i7 with 12 gigs of ram isn't too expensive and I'd use that for the MySQL server if you're going with monolithic data-store).