WordPress.org

Support

Support » How-To and Troubleshooting » Blog with 50k posts / 300k comments – slow msyql queries

Blog with 50k posts / 300k comments – slow msyql queries

  • I’m pretty new to this mysql stuff but i’ve been doing alot of reading.

    I setup log slow queries to shows queries that are slow than 1. I stripped the site of all plugins except the caches. Here’s some examples:

    ->  Somebody looking at posts by author?
    
    # Query_time: 2  Lock_time: 0  Rows_sent: 20  Rows_examined: 56540
    SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND (wp_posts.post_author = 2) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  ORDER BY wp_posts.post_date DESC LIMIT 4840, 20;
    
    -> Viewing a single post?
    
    # Query_time: 3  Lock_time: 0  Rows_sent: 1  Rows_examined: 165587
    SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)  WHERE 1=1  AND wp_term_taxonomy.taxonomy = 'category'  AND wp_term_taxonomy.term_id IN ('77', '1', '58', '63', '64')  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 1;
    
    -> Viewing a category
    
    # Query_time: 2  Lock_time: 0  Rows_sent: 20  Rows_examined: 145236
    SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)  WHERE 1=1  AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('1') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 21340, 20;

    These queries eat up almost all of the CPU time and there’s hundreds of them running. Luckily the server is surviving. The problem comes when I try to Publish new posts and Approve New comments. Those two actions bring down the server.

    I think my problem is that the normal wordpress queries are not using indexes to return data. I ran myisamchk -r on all the MYI tables. It fixed the indexes but i still get those slow queries above.

    I’ve been searching but i cannot find any wordpress documentation for dealing with large databases and adding indexes to make queries faster. Can anybody point me int he right direction?

Viewing 2 replies - 1 through 2 (of 2 total)
Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Blog with 50k posts / 300k comments – slow msyql queries’ is closed to new replies.
Skip to toolbar