Why is this SQL query extremely slow?

Question

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

Answer 1

95% of systems are better off with the Query Cache turned off. Remember that any modification to a table causes the QC to purge all entries for that table. If there are a lot of writes to a table, that could be a lot of CPU cycles for purging.

If you have only 1GB of RAM, there is not much room for the QC; use the room for InnoDB's buffer_pool instead. When you changed from MyISAM did you lower key_buffer_size and raise innodb_buffer_pool_size?

Some of these composite indexes may help:

c:  INDEX(post_id, meta_key,  meta_value)
v:  INDEX(wix_id, store_id, wc_id,  original_price_rand, price)
s:  INDEX(meta_key, post_id,  meta_value)
bp:  INDEX(user_id, tier)
t:  INDEX(role_name,  discount_percent)
tag:  INDEX(size_label,  extra_price_tag)
lab:  INDEX(size_label,  extra_price_label)
i:  INDEX(wix_variation_id,  uri, branded, design_cost)
ib:  INDEX(wix_variation_id,  uri, branded, design_cost)
rt:  INDEX(user_id,  size_label)
rl:  INDEX(user_id,  size_label)

When adding a composite index, DROP index(es) with the same leading columns. That is, when you have both INDEX(a) and INDEX(a,b), toss the former.