MySQL query is taking a long time to execute

mysql-5.6

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`),
)

Best Answer

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.

Related Question