0

I have a performance issue with a MySQL query that takes a long time. I'm trying to figure out why and what is Block nested loop?

MySQL Version is 5.6 and Running on Cloud

The query that works but takes a long time:

SELECT wp_posts.ID, post_title, post_content, wp_pvc_total.postcount, wp_pvc_total.postnum 
FROM wp_posts 
LEFT JOIN wp_term_relationships ON ( wp_posts.ID = wp_term_relationships.object_id ) 
LEFT JOIN wp_term_taxonomy ON ( wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id ) 
LEFT JOIN wp_pvc_total ON ( wp_pvc_total.postnum = wp_posts.ID ) 
WHERE wp_term_taxonomy.term_id IN (38) 
GROUP BY wp_posts.ID 
ORDER BY wp_pvc_total.postcount DESC;

This query takes about 1 min averagely and returns 33 rows:

*************************** 1. row *************************** id: 1 select_type: SIMPLE table: wp_term_taxonomy type: ref possible_keys: PRIMARY,term_id_taxonomy key: term_id_taxonomy key_len: 8 ref: const rows: 1 Extra: Using where; Using index; Using temporary; Using filesort

*************************** 2. row *************************** id: 1 select_type: SIMPLE table: wp_term_relationships type: ref possible_keys: PRIMARY,term_taxonomy_id key: term_taxonomy_id key_len: 8 ref: marriai1_topic.wp_term_taxonomy.term_taxonomy_id rows: 2 Extra: Using where; Using index

*************************** 3. row *************************** id: 1 select_type: SIMPLE table: wp_posts type: eq_ref possible_keys: PRIMARY,post_name,type_status_date,post_parent,post_author key: PRIMARY key_len: 8 ref: marriai1_topic.wp_term_relationships.object_id rows: 1 Extra: NULL

*************************** 4. row *************************** id: 1 select_type: SIMPLE table: wp_pvc_total type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 15547 Extra: Using where; Using join buffer (Block Nested Loop)

Below is the structure of involved tables in this Query

Table: wp_term_taxonomy Create Table:

CREATE TABLE `wp_term_taxonomy` (
  `term_taxonomy_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `term_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `taxonomy` varchar(32) NOT NULL DEFAULT '',
  `description` longtext NOT NULL,
  `parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `count` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`term_taxonomy_id`),
  UNIQUE KEY `term_id_taxonomy` (`term_id`,`taxonomy`),
  KEY `taxonomy` (`taxonomy`)
)

Table: wp_term_relationships Create Table:

CREATE TABLE `wp_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`)
)

Table: wp_posts Create Table:

CREATE TABLE `wp_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 `post_name` (`post_name`(191)),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
)

Table: wp_pvc_total Create Table:

CREATE TABLE `wp_pvc_total` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `postnum` varchar(255) NOT NULL,
  `postcount` int(11) NOT NULL DEFAULT '750',
  UNIQUE KEY `id` (`id`),
)
1
  • At least 2 of JOINs must be replaced with INNER. – Akina Oct 14 '19 at 9:59
1

wp_pvc_total.postnum and wp_posts.id are conflicting data types and you are joining on them. This is going to force bad internal paths to be taken.

You should see better performance by changing wp_pvc_total.postnum to an int and indexing it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.