CODEWITHSUNDEEP
mysqlwordpressquery-optimization
Jan
Jan

Reputation: 35

optimize the mysql query

I have following mysql query which is taking long time(40s) to load the results.

SELECT SQL_CALC_FOUND_ROWS blog_posts.ID FROM blog_posts 
LEFT JOIN blog_term_relationships AS tt0 ON (blog_posts.ID = tt0.object_id) 
LEFT JOIN blog_term_relationships AS tt1 ON (blog_posts.ID = tt1.object_id) 
LEFT JOIN blog_term_relationships AS tt2 ON (blog_posts.ID = tt2.object_id) 
LEFT JOIN blog_term_relationships AS tt3 ON (blog_posts.ID = tt3.object_id) 
WHERE 1=1  
AND ( ( tt0.term_taxonomy_id IN (141,177) AND tt1.term_taxonomy_id IN (2389,2390) ) 
OR ( tt2.term_taxonomy_id IN (167,1169,1715) AND tt3.term_taxonomy_id IN (2519,2520) ) ) 
AND blog_posts.post_type = 'post' AND (blog_posts.post_status = 'publish') 
GROUP BY blog_posts.ID ORDER BY blog_posts.post_date ASC LIMIT 0, 20

Is there any way to optimize this query.

Edit:

This is related to wordpress and this query was automatically create from the wp_query.

Table structures as bellow,

blog_posts table:

CREATE TABLE `blog_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_excerpt` text NOT NULL,
  `post_status` varchar(20) NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) NOT NULL DEFAULT 'open',
  `post_password` varchar(255) NOT NULL DEFAULT '',
  `post_name` varchar(200) NOT NULL DEFAULT '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT '0',
  `post_type` varchar(20) NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`),
  KEY `post_name` (`post_name`(191))
) ENGINE=MyISAM AUTO_INCREMENT=125636 DEFAULT CHARSET=utf8

blog_term_relationships table:

CREATE TABLE `blog_term_relationships` (
  `object_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `term_taxonomy_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `term_order` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`object_id`,`term_taxonomy_id`),
  KEY `term_taxonomy_id` (`term_taxonomy_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

EXPLAIN QUERY:

enter image description here

Upvotes: 3

Views: 97

Answers (1)

Rick James
Rick James

Reputation: 142298

Reformulate

SELECT  SQL_CALC_FOUND_ROWS blog_posts.ID
    FROM  ( 
            (
                SELECT  object_id
                    FROM  blog_term_relationships AS tt0
                    JOIN  blog_term_relationships AS tt1 USING(object_id)
                    WHERE  tt0.term_taxonomy_id IN (141,177)
                      AND  tt1.term_taxonomy_id IN (2389,2390)

            )
        UNION DISTINCT
            (
                SELECT  object_id
                    FROM  blog_term_relationships AS tt2
                    JOIN  blog_term_relationships AS tt3 USING(object_id)
                    WHERE  tt2.term_taxonomy_id IN (167,1169,1715)
                      AND  tt3.term_taxonomy_id IN (2519,2520) ) 
          ) AS tt
    JOIN  blog_posts  ON blog_posts.ID = tt.object_id
    WHERE  blog_posts.post_type = 'post'
      AND  blog_posts.post_status = 'publish'
    ORDER BY  blog_posts.post_date ASC
    LIMIT  0, 20

This gets rid of the GROUP BY and does several other things to speed up the query.

Prefix index

`post_name` varchar(200) NOT NULL DEFAULT '',
KEY `post_name` (`post_name`(191))
) ENGINE=MyISAM AUTO_INCREMENT=125636 DEFAULT CHARSET=utf8

Make up your mind -- 191 is for version 5.6 with utf8mb4 (which you have not specified); 191 is so close to 200, you may as well make it VARCHAR(191). Getting rid of the prefix index is likely to speed up some of your queries.

InnoDB

Don't use MyISAM, move to InnoDB. That is for performance, robustness, etc. That will coincidentally fix an inefficiency in KEY term_taxonomy_id).

SQL_CALC_FOUND_ROWS

SQL_CALC_FOUND_ROWS is costly. It's purpose is passe.

Upvotes: 2

Related Questions