MySQL Slow query problem

  • My host shows our site being problematic when it comes to the particular query below. It takes 2-3 seconds to complete.

    Any ideas on how to speed this up? Do we have a DB that’s just too big on these three tables?

    Created 06:55 PM 07/28/2008

    ### 74 Queries
    ### Total time: 154, Average time: 2.08108108108108
    ### Taking 2 to 3 seconds to complete
    ### Rows analyzed 55576 – 56375
    SELECT t.*, tt.*
    FROM wp_terms AS t
    INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
    INNER JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
    INNER JOIN wp_posts AS p ON tr.object_id = p.ID
    WHERE tt.taxonomy IN ( ‘XXX’ )
    AND p.post_date_gmt < ‘XXX’

    AND tt.count > XXX

    GROUP BY t.term_id
    ORDER BY tt.count DESC
    LIMIT XXX;

    SELECT t.*, tt.*
    FROM wp_terms AS t
    INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
    INNER JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
    INNER JOIN wp_posts AS p ON tr.object_id = p.ID
    WHERE tt.taxonomy IN ( ‘post_tag’ )
    AND p.post_date_gmt < ‘2008-07-28 17:49:23’

    AND tt.count > 0

    GROUP BY t.term_id
    ORDER BY tt.count DESC
    LIMIT 45;

Viewing 31 replies (of 31 total)

  • I am having this exact problem right now. Any help would be MUCH appreciated.

    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 (‘3′, ’74’, ’11’, ‘7’, ‘8’, ‘9’, ’10’, ’14’, ‘4’, ‘5’, ’80’, ’69’, ’15’, ’13’, ’12’, ’77’, ’78’, ’76’, ’75’, ’79’, ’43’, ’44’, ’42’, ’41’, ’91’, ’51’, ’52’, ’50’, ’49’, ’92’, ’55’, ’56’, ’54’, ’53’, ’93’, ’59’, ’60’, ’58’, ’57’, ’94’, ’63’, ’64’, ’62’, ’61’, ’95’, ’25’, ’28’, ’26’, ’24’, ’86’, ’18’, ’19’, ’17’, ’16’, ’96’, ’21’, ’23’, ’22’, ’20’, ’97’, ’83’, ’84’, ’82’, ’81’, ’85’, ’72’, ’73’, ’71’, ’70’, ’87’, ’36’, ’35’, ’34’, ’33’, ’89’, ’31’, ’32’, ’30’, ’29’, ’88’, ’39’, ’40’, ’38’, ’37’, ’90’) AND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’) LIMIT 0, 4;

    We have been in the process of moving a site with 40k posts from custom software to WordPress and this is really causing a problem.

Viewing 31 replies (of 31 total)
  • The topic ‘MySQL Slow query problem’ is closed to new replies.