Speeding up a (slow) huge wordpress database

I’m testing out a new project which involves the use of a wordpress installation which has over 1.5 million posts, generally the post content/title is only a line or two – so quite short.

I’ve already got the highly recommended W3-cache plugin, which helps a lot – but when you first land on a page it takes a good 40-60 to load and generate a cache of it, and with a site that contains over 1 million posts I’m guessing having them all cached is going to be a disaster – as only about 5% of these will be viewed on a regular basis.

Below is what’s going on for a standard build of a post, is there anything I can do to change/speed up the obvious bottle neck? I’m not even sure what the JOIN is doing? surely all that needs to happen is get post by ID. The query that’s taking so long looks like it’s a query to show a number of posts and order them based on metadata – which I don’t need on the post page?

 [5] => Array
    (
        [0] =>  SELECT   wp_posts.* FROM wp_posts  INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') AND (wp_postmeta.meta_key = 'wpfp_favorites' ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 ASC LIMIT 0, 1
        [1] => 43.2097918987
        [2] => require, require_once, include, get_header, locate_template, load_template, require_once, wp_head, do_action, call_user_func_array, start_post_rel_link, get_boundary_post_rel_link, get_boundary_post, get_posts, WP_Query->query, WP_Query->get_posts, W3_Db->query
    )

[6] => Array
    (
        [0] => SELECT p.* FROM wp_posts AS p  WHERE p.post_date < '0000-00-00 00:00:00' AND p.post_type = 'post' AND p.post_status = 'publish'  ORDER BY p.post_date DESC LIMIT 1
        [1] => 7.29560852051E-5
        [2] => require, require_once, include, get_header, locate_template, load_template, require_once, wp_head, do_action, call_user_func_array, adjacent_posts_rel_link_wp_head, adjacent_posts_rel_link, get_adjacent_post_rel_link, get_adjacent_post, W3_Db->query
    )

[7] => Array
    (
        [0] => SELECT p.* FROM wp_posts AS p  WHERE p.post_date > '0000-00-00 00:00:00' AND p.post_type = 'post' AND p.post_status = 'publish'  ORDER BY p.post_date ASC LIMIT 1
        [1] => 1.78813934326E-5
        [2] => require, require_once, include, get_header, locate_template, load_template, require_once, wp_head, do_action, call_user_func_array, adjacent_posts_rel_link_wp_head, adjacent_posts_rel_link, get_adjacent_post_rel_link, get_adjacent_post, W3_Db->query
    )

[8] => Array
    (
        [0] => SELECT option_value FROM wp_options WHERE option_name = 'theme_mods_twentyeleven' LIMIT 1
        [1] => 1.00135803223E-5
        [2] => require, require_once, include, get_header, locate_template, load_template, require_once, wp_head, do_action, call_user_func_array, _custom_background_cb, get_background_image, get_theme_mod, get_theme_mods, get_option, W3_Db->query
    )

[9] => Array
    (
        [0] => SELECT option_value FROM wp_options WHERE option_name = 'mods_Twenty Eleven' LIMIT 1
        [1] => 8.82148742676E-6
        [2] => require, require_once, include, get_header, locate_template, load_template, require_once, wp_head, do_action, call_user_func_array, _custom_background_cb, get_background_image, get_theme_mod, get_theme_mods, get_option, W3_Db->query
    )

[10] => Array
    (
        [0] => SELECT t.*, tt.* 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 ('post_format') AND tr.object_id IN (1034759) ORDER BY t.name ASC
        [1] => 1.31130218506E-5
        [2] => require, require_once, include, get_header, locate_template, load_template, require_once, body_class, get_body_class, get_post_format, get_the_terms, wp_get_object_terms, W3_Db->query
    )

[11] => Array
    (
        [0] => SELECT DISTINCT post_author FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' LIMIT 2
        [1] => 1.31130218506E-5
        [2] => require, require_once, include, get_header, locate_template, load_template, require_once, body_class, get_body_class, apply_filters, call_user_func_array, twentyeleven_body_classes, is_multi_author, W3_Db->query
    )

[12] => Array
    (
        [0] => SELECT * FROM wp_posts  WHERE (post_type = 'page' AND post_status = 'publish')  AND ( ID <> 1232798 )    ORDER BY menu_order,wp_posts.post_title ASC
        [1] => 1.00135803223E-5
        [2] => require, require_once, include, get_header, locate_template, load_template, require_once, wp_nav_menu, call_user_func, wp_page_menu, wp_list_pages, get_pages, W3_Db->query
    )

[13] => Array
    (
        [0] => SELECT * FROM wp_users WHERE ID = 4031 LIMIT 1
        [1] => 2.00271606445E-5
        [2] => require, require_once, include, the_post, WP_Query->the_post, setup_postdata, get_userdata, W3_Db->query
    )

[14] => Array
    (
        [0] => SELECT t.*, tt.* 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 ('post_tag') AND tr.object_id IN (1034759) ORDER BY t.name ASC
        [1] => 1.78813934326E-5
        [2] => require, require_once, include, get_template_part, locate_template, load_template, require, post_class, get_post_class, get_the_tags, get_the_terms, wp_get_object_terms, W3_Db->query
    )

[15] => Array
    (
        [0] => SELECT * FROM wp_comments  WHERE comment_approved = '1' AND comment_post_ID = 1034759 ORDER BY comment_date_gmt ASC 
        [1] => 2.09808349609E-5
        [2] => require, require_once, include, comments_template, get_comments, WP_Comment_Query->query, W3_Db->query
    )

[16] => Array
    (
        [0] => SELECT post_id, meta_value, post_status FROM wp_postmeta LEFT JOIN wp_posts ON post_id=wp_posts.ID WHERE post_status='publish' AND meta_key='wpfp_favorites' AND meta_value > 0 ORDER BY ROUND(meta_value) DESC LIMIT 0, 5
        [1] => 1.50203704834E-5
        [2] => require, require_once, include, get_sidebar, locate_template, load_template, require_once, dynamic_sidebar, call_user_func_array, wpfp_widget_view, wpfp_list_most_favorited, W3_Db->query
    )

Regardless of the above question, I’m on shared hosting at the momment – so obviously it’s not going to cut it, what I want to ask is if you where running this sort of site – what sort of server specs/hosting plan would you be looking at to handle this size of installation? With a few thouand vistors to start per week moving up.