Why is this SQL query extremely slow?-CodePudding

UPDATE: seems that the Query Cache is not activated, otherwise the performance would be much better from the second execution of the same query, since MySQL should have cached it already. I have MariaDB version: 10.2.41- Protocol version: 10 , indeed the Query Cache is disabled by default starting with MariaDB 10.1.7. Is it a good idea to enable it on my VPS config? Or i better upgrade it first?

I have a MySQL query performing very slowly on a CentOS VPS with 1GB RAM and 1 CPU Core.

The query takes 400 - 900 ms to execute (!!), which i want to figure out how to optimize.

It's over several tables, some of them pretty huge (Wordpress' wp_postmeta for example). But apparently all columns involved are indexed properly, so i'm asking for advice on what to do, or if the only optimization possible is to upgrade the VPS.

Thanks in advance!

Here is the query and the EXPLAIN output

EXPLAIN SELECT c.post_id AS wc_variation_id,v.original_price_rand,v.price,c.meta_value AS color,s.meta_value AS size,bp.tier,t.discount_percent,MAX(IFNULL(tag.extra_price_tag,0)) AS extra_price_tag,MAX(IFNULL(lab.extra_price_label,0)) AS extra_price_label,

i.uri,i.branded,i.design_cost,ib.uri AS uri_back,ib.branded AS branded_back,ib.design_cost AS design_cost_back
FROM wp_postmeta c
JOIN brandly_tiers_bp_balances bp
JOIN brandly_tiers t ON t.role_name=bp.tier
JOIN wix_images i
JOIN wix_images_back ib
JOIN wix_imported_product_variations v ON v.wc_id=c.post_id
LEFT JOIN brandly_reseller_tags rt ON rt.user_id=bp.user_id
LEFT JOIN brandly_reseller_branded_packaging_sizes tag ON tag.size_label=rt.size_label
LEFT JOIN brandly_reseller_labels rl ON rl.user_id=bp.user_id
LEFT JOIN brandly_reseller_branded_packaging_sizes lab ON lab.size_label=rl.size_label
JOIN wp_postmeta s ON s.post_id=c.post_id
WHERE c.post_id IN (SELECT DISTINCT(wc_id) FROM wix_imported_product_variations WHERE wc_product_id = 181189)
AND s.meta_key = 'attribute_pa_size'
AND c.meta_key = 'attribute_pa_colour'
AND i.wix_variation_id=v.wix_id
AND ib.wix_variation_id=v.wix_id
AND v.store_id=36
AND bp.user_id=11
GROUP BY c.post_id
ORDER BY color,size

Output:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	bp	const	PRIMARY,tier	PRIMARY	8	const	1	Using temporary; Using filesort
1	PRIMARY	t	const	PRIMARY	PRIMARY	52	const	1
1	PRIMARY	wix_imported_product_variations	ref	wc_product_id,wc_id	wc_product_id	8	const	3	Start temporary
1	PRIMARY	rt	ref	PRIMARY	PRIMARY	8	const	5	Using index
1	PRIMARY	tag	eq_ref	PRIMARY	PRIMARY	32	brandly_xvfkg.rt.size_label	1	Using where
1	PRIMARY	c	ref	post_id,meta_key	post_id	8	brandly_xvfkg.wix_imported_product_variations.wc_id	14	Using index condition; Using where; End temporary
1	PRIMARY	v	ref	PRIMARY,store_id,wc_id	wc_id	8	brandly_xvfkg.c.post_id	1	Using index condition; Using where
1	PRIMARY	ib	ref	wix_variation_id	wix_variation_id	103	brandly_xvfkg.v.wix_id	3
1	PRIMARY	i	ref	wix_variation_id	wix_variation_id	103	brandly_xvfkg.v.wix_id	3
1	PRIMARY	s	ref	post_id,meta_key	post_id	8	brandly_xvfkg.c.post_id	14	Using where
1	PRIMARY	rl	ref	PRIMARY	PRIMARY	8	const	5	Using index
1	PRIMARY	lab	eq_ref	PRIMARY	PRIMARY	32	brandly_xvfkg.rl.size_label	1	Using where

EDIT: The subquery takes 0.0005 seconds , so it's not the culprit

EDIT2: the wp_postmeta table has 1709642 records and it's MyISAM:

SHOW CREATE TABLE `wp_postmeta`;
CREATE TABLE `wp_postmeta` (
 `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `post_id` bigint(20) unsigned NOT NULL DEFAULT 0,
 `meta_key` varchar(255) DEFAULT NULL,
 `meta_value` longtext DEFAULT NULL,
 PRIMARY KEY (`meta_id`),
 KEY `post_id` (`post_id`),
 KEY `meta_key` (`meta_key`(191))
) ENGINE=MyISAM AUTO_INCREMENT=3945402 DEFAULT CHARSET=utf8

EDIT3: actually all tables are MyISAM except wix_images, wix_images_back, wix_imported_product_variations which are innoDB

EDIT4: just converted all tables to innoDB. The performance is better now, it takes 200-400 ms. Still slow though.

EDIT5: EXPLAIN output changed a bit now, after switching the tables to innoDB. See https://codebeautify.org/htmlviewer/y225302bb

EDIT6: here are the definitions of the tables involved

CREATE TABLE `wp_postmeta` (
 `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `post_id` bigint(20) unsigned NOT NULL DEFAULT 0,
 `meta_key` varchar(255) DEFAULT NULL,
 `meta_value` longtext DEFAULT NULL,
 PRIMARY KEY (`meta_id`),
 KEY `post_id` (`post_id`),
 KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=3945402 DEFAULT CHARSET=utf8

CREATE TABLE `brandly_tiers_bp_balances` (
 `user_id` bigint(20) NOT NULL,
 `bp_balance` bigint(20) NOT NULL DEFAULT 0,
 `bp_pending_balance` bigint(20) NOT NULL DEFAULT 0,
 `tier` varchar(50) COLLATE latin1_general_ci NOT NULL DEFAULT 'Earth',
 `tier_change_ts` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`user_id`),
 KEY `tier` (`tier`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

CREATE TABLE `brandly_tiers` (
 `role_name` varchar(50) COLLATE latin1_general_ci NOT NULL,
 `bp_threshold` int(11) NOT NULL DEFAULT 0,
 `discount_percent` smallint(6) NOT NULL,
 `bp_percent_branded_orders` smallint(6) NOT NULL,
 `bp_percent_bulk_orders` smallint(6) NOT NULL,
 `bp_percent_courier` smallint(6) NOT NULL,
 `bp_percent_bulk_branded_packaging` smallint(6) NOT NULL,
 `bp_percent_monthly_subscription_fee` smallint(6) NOT NULL,
 `bulk_order_minimum_quantity` int(11) NOT NULL DEFAULT 10,
 `bp_bulk_order_cap` int(11) NOT NULL DEFAULT 7500,
 `tier_monthly_subscription_price` int(11) NOT NULL DEFAULT 0,
 PRIMARY KEY (`role_name`),
 UNIQUE KEY `bp_threshold` (`bp_threshold`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

CREATE TABLE `wix_images` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `filename` text COLLATE latin1_general_ci NOT NULL,
 `uri` longtext COLLATE latin1_general_ci NOT NULL,
 `background_image_uri` longtext COLLATE latin1_general_ci NOT NULL,
 `branded` tinyint(1) DEFAULT 0,
 `wix_id` varchar(100) COLLATE latin1_general_ci DEFAULT NULL,
 `wix_product_id` varchar(100) COLLATE latin1_general_ci DEFAULT NULL,
 `wix_variation_id` varchar(100) COLLATE latin1_general_ci DEFAULT NULL,
 `design_cost` decimal(18,2) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `wix_id` (`wix_id`),
 KEY `wix_product_id` (`wix_product_id`),
 KEY `wix_variation_id` (`wix_variation_id`),
 KEY `branded` (`branded`),
 KEY `background_image_uri` (`background_image_uri`(8))
) ENGINE=InnoDB AUTO_INCREMENT=10680 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

CREATE TABLE `wix_images_back` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `filename` text COLLATE latin1_general_ci NOT NULL,
 `uri` longtext COLLATE latin1_general_ci NOT NULL,
 `background_image_uri` longtext COLLATE latin1_general_ci NOT NULL,
 `branded` tinyint(1) DEFAULT 0,
 `wix_id` varchar(100) COLLATE latin1_general_ci DEFAULT NULL,
 `wix_product_id` varchar(100) COLLATE latin1_general_ci DEFAULT NULL,
 `wix_variation_id` varchar(100) COLLATE latin1_general_ci DEFAULT NULL,
 `design_cost` decimal(18,2) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `wix_id` (`wix_id`),
 KEY `wix_product_id` (`wix_product_id`),
 KEY `wix_variation_id` (`wix_variation_id`),
 KEY `branded` (`branded`),
 KEY `background_image_uri` (`background_image_uri`(8))
) ENGINE=InnoDB AUTO_INCREMENT=7733 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

CREATE TABLE `wix_imported_product_variations` (
 `wix_id` varchar(100) COLLATE latin1_general_ci NOT NULL,
 `store_id` int(11) NOT NULL,
 `wc_id` bigint(20) NOT NULL,
 `wc_product_id` bigint(20) NOT NULL,
 `sku` varchar(100) COLLATE latin1_general_ci NOT NULL,
 `price` decimal(18,2) NOT NULL,
 `original_price_rand` decimal(18,2) NOT NULL,
 `has_markup` tinyint(1) DEFAULT 1,
 `wix_image_id` varchar(1000) COLLATE latin1_general_ci NOT NULL,
 `imported_at` datetime DEFAULT NULL,
 `tmp_design_image` text COLLATE latin1_general_ci DEFAULT NULL,
 `tmp_branded` tinyint(1) DEFAULT NULL,
 `tmp_design_cost` decimal(18,2) DEFAULT NULL,
 `tmp_design_image_back` text COLLATE latin1_general_ci DEFAULT NULL,
 `tmp_branded_back` tinyint(1) DEFAULT NULL,
 `tmp_design_cost_back` decimal(18,2) DEFAULT NULL,
 PRIMARY KEY (`wix_id`),
 KEY `sku` (`sku`),
 KEY `store_id` (`store_id`),
 KEY `has_markup` (`has_markup`),
 KEY `imported_at` (`imported_at`),
 KEY `wc_product_id` (`wc_product_id`),
 KEY `wc_id` (`wc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

CREATE TABLE `brandly_reseller_tags` (
 `user_id` bigint(20) NOT NULL,
 `size_label` varchar(30) COLLATE latin1_general_ci NOT NULL,
 `image_front1` mediumtext COLLATE latin1_general_ci NOT NULL,
 `image_back1` mediumtext COLLATE latin1_general_ci NOT NULL,
 `image_front2` mediumtext COLLATE latin1_general_ci NOT NULL,
 `image_back2` mediumtext COLLATE latin1_general_ci NOT NULL,
 `image_front3` mediumtext COLLATE latin1_general_ci NOT NULL,
 `image_back3` mediumtext COLLATE latin1_general_ci NOT NULL,
 `default_index` int(11) DEFAULT NULL,
 `design_cost` decimal(18,2) DEFAULT NULL,
 PRIMARY KEY (`user_id`,`size_label`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

CREATE TABLE `brandly_reseller_labels` (
 `user_id` bigint(20) NOT NULL,
 `size_label` varchar(30) COLLATE latin1_general_ci NOT NULL,
 `image_front1` mediumtext COLLATE latin1_general_ci NOT NULL,
 `image_back1` mediumtext COLLATE latin1_general_ci NOT NULL,
 `image_front2` mediumtext COLLATE latin1_general_ci NOT NULL,
 `image_back2` mediumtext COLLATE latin1_general_ci NOT NULL,
 `image_front3` mediumtext COLLATE latin1_general_ci NOT NULL,
 `image_back3` mediumtext COLLATE latin1_general_ci NOT NULL,
 `default_index` int(11) DEFAULT NULL,
 `design_cost` decimal(18,2) DEFAULT NULL,
 PRIMARY KEY (`user_id`,`size_label`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

    
CREATE TABLE `brandly_reseller_branded_packaging_sizes` (
 `size_label` varchar(30) COLLATE latin1_general_ci NOT NULL,
 `size1_cm` int(11) NOT NULL,
 `size2_cm` int(11) NOT NULL,
 `size3_cm` int(11) NOT NULL,
 `is_bag` tinyint(1) DEFAULT NULL,
 `max_weight_kg` decimal(18,2) NOT NULL,
 `extra_price` decimal(18,2) NOT NULL,
 `extra_price_gift` decimal(18,2) NOT NULL,
 `extra_price_tag` decimal(18,2) NOT NULL,
 `extra_price_label` decimal(18,2) NOT NULL,
 PRIMARY KEY (`size_label`),
 KEY `size1_cm` (`size1_cm`),
 KEY `size2_cm` (`size2_cm`),
 KEY `size3_cm` (`size3_cm`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

EDIT7: moved the ON conditions from the JOINs to the WHERE. Execution time 200-400ms like before.

EXPLAIN SELECT c.post_id AS wc_variation_id,v.original_price_rand,v.price,c.meta_value AS color,s.meta_value AS size,bp.tier,t.discount_percent,MAX(IFNULL(tag.extra_price_tag,0)) AS extra_price_tag,MAX(IFNULL(lab.extra_price_label,0)) AS extra_price_label,

i.uri,i.branded,i.design_cost,ib.uri AS uri_back,ib.branded AS branded_back,ib.design_cost AS design_cost_back
FROM wp_postmeta c
JOIN brandly_tiers_bp_balances bp
JOIN brandly_tiers t
JOIN wix_images i
JOIN wix_images_back ib
JOIN wix_imported_product_variations v 
LEFT JOIN brandly_reseller_tags rt ON rt.user_id=bp.user_id
LEFT JOIN brandly_reseller_branded_packaging_sizes tag ON tag.size_label=rt.size_label
LEFT JOIN brandly_reseller_labels rl ON rl.user_id=bp.user_id
LEFT JOIN brandly_reseller_branded_packaging_sizes lab ON lab.size_label=rl.size_label
JOIN wp_postmeta s 
WHERE c.post_id IN (SELECT DISTINCT(wc_id) FROM wix_imported_product_variations WHERE wc_product_id = 181189)
AND s.meta_key = 'attribute_pa_size'
AND c.meta_key = 'attribute_pa_colour'
AND i.wix_variation_id=v.wix_id
AND ib.wix_variation_id=v.wix_id
AND v.store_id=36
AND bp.user_id=11
AND s.post_id=c.post_id
AND t.role_name=bp.tier
AND v.wc_id=c.post_id
GROUP BY c.post_id
ORDER BY color,size

EXPLAIN output

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	bp	const	PRIMARY,tier	PRIMARY	8	const	1	Using temporary; Using filesort
1	PRIMARY	t	const	PRIMARY	PRIMARY	52	const	1
1	PRIMARY	wix_imported_product_variations	ref	wc_product_id,wc_id	wc_product_id	8	const	3	Start temporary
1	PRIMARY	rt	ref	PRIMARY	PRIMARY	8	const	5	Using index
1	PRIMARY	tag	eq_ref	PRIMARY	PRIMARY	32	brandly_xvfkg.rt.size_label	1	Using where
1	PRIMARY	c	ref	post_id,meta_key	post_id	8	brandly_xvfkg.wix_imported_product_variations.wc_id	8	Using index condition; Using where; End temporary
1	PRIMARY	v	ref	PRIMARY,store_id,wc_id	wc_id	8	brandly_xvfkg.c.post_id	1	Using index condition; Using where
1	PRIMARY	ib	ref	wix_variation_id	wix_variation_id	103	brandly_xvfkg.v.wix_id	3
1	PRIMARY	i	ref	wix_variation_id	wix_variation_id	103	brandly_xvfkg.v.wix_id	3
1	PRIMARY	s	ref	post_id,meta_key	post_id	8	brandly_xvfkg.v.wc_id	8	Using index condition; Using where
1	PRIMARY	rl	ref	PRIMARY	PRIMARY	8	const	5	Using index
1	PRIMARY	lab	eq_ref	PRIMARY	PRIMARY	32	brandly_xvfkg.rl.size_label	1	Using where

CodePudding user response：

Spending more money on a bigger server is unlikely to do you much good.
200-400ms for a query like this is not bad. There's probably no SQL magic that will get it to under 100ms; it's doing a lot. Often the words extremely slow refer to queries that take minutes or longer.
MAX(IFNULL(column, 0)) yields precisely the same result as MAX(column) as long as all values of column are positive.. Aggregate functions skip over nulls. You might be able to simplify the two places you use that expression.
Your query is hard to reason about. If this were my query I would rewrite it to get rid of the short table aliases, and rather spell them out. And, I would rewrite it so every JOIN (both LEFT and ordinary) had an ON-clause with it. Notice that ON-clauses can be compound:
```
FROM a
JOIN wp_postmeta   ON a.id = wp_postmeta.postid
                   AND wp_postmeta.meta_key = 'attribute_pa_size'
```
That's a query pattern we WordPress hackers instantly recognize.

For ordinary JOINs, moving ON-clauses to WHERE-clauses has no effect on execution plans.
Your join types, in your plan, are all "const", "eq-ref", or "ref". That is very good.
There's a field-proven rework of WordPress's postmeta table indexes. Your postmeta table is big enough -- almost four megarows -- that you probably can use it. It changes the primary key to a compound key that meets the requirement of your query pattern. Yours is a common pattern in WooCommerce installations. It is this for MySQL 5.7 and above.
```
ALTER TABLE wp_postmeta
   ADD UNIQUE KEY meta_id (meta_id),
   DROP PRIMARY KEY,
   ADD PRIMARY KEY (post_id, meta_key, meta_id),
  DROP KEY meta_key,
   ADD KEY meta_key (meta_key, meta_value(32), post_id, meta_id),
   ADD KEY meta_value (meta_value(32), meta_id),
  DROP KEY post_id;
```
The Index WP MySQL For Speed plugin is designed to install this set of indexes for you, and do its best with MySQL / MariaDB 5.5 and up.
I don't see any big problems with indexing on your non-WordPress tables. There may be some opportunities for compound covering indexes, but, again, your query is hard to read.