WordPress.org

Ready to get started?Download WordPress

Forums

slow mysql queries - group by (2 posts)

  1. burekc
    Member
    Posted 5 years ago #

    I have a WordPress site with over 200,000 posts. It has been running slow and we've narrowed the cause down to a particular SQL query (see below). Specifically the group by and order by are causing the slowness. Has anyone had the same problem? Have you been able to solve it?

    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 ('56', '18', '32', '34', '33', '12', '40', '47', '49', '52', '9', '29', '20', '17', '8', '11', '28', '16', '42', '25', '55', '43', '13', '35', '26', '36', '37', '44', '51', '46', '7', '6', '39', '27', '10', '31', '54', '22', '23', '24', '48', '15', '14', '53', '41', '21', '30', '19', '45', '50', '38') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 20

  2. alberto@tomato
    Member
    Posted 5 years ago #

    Hi burekc, same problem here.
    EXPLAIN says that the query is "Using where; Using temporary; Using filesort" - and with large tables this is evil.
    My posts table is half yours, but performances are equally poor. These are useful resources:

    http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html
    http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

    I'm trying to get to the point. Do you have some news?
    Thanks.

Topic Closed

This topic has been closed to new replies.

About this Topic