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

try changing `SELECT ` to `SELECT straight_join `; that should keep it from trying to find the brandly_tiers_bp_balances rows by user_id first — ysth, May 19 '22 at 19:42
@ysth it takes 3-3.6 seconds, so it's worse. Here is the explain output for it https://codebeautify.org/htmlviewer/y22c8c47e — developpper, May 19 '22 at 19:55
Are you able to change this query? Please [edit] your question to tell us. The wp_postmeta table's structure is [well known](https://codex.wordpress.org/Database_Description). Not so well known are the brandly* and wix_images tables. You could show us those definitions. This [WordPress plugin](https://wordpress.org/plugins/index-wp-mysql-for-speed/) may help you with your postmeta lookups if you're willing to switch to InnoDB. — O. Jones, May 19 '22 at 21:43
@O.Jones yes i can edit the query. Edited the question with table structures. Also, now i have already switched to innoDB, see the EDITs to my question. — developpper, May 19 '22 at 22:00
Can the three JOIN lines with NO ON lsxx=rxss qualifiers be made to look more like the other JOIN's and provide your direction on WHERE to JOIN the tables? Need EXPLAIN refreshed with current query results, please. — Wilson Hauck, May 19 '22 at 22:16
Could you post TEXT results of SHOW CREATE TABLE for these 3 tables? wix_images wix_images_back JOIN wix_imported_product_variations ? Thanks — Wilson Hauck, May 19 '22 at 22:32
You might consider keeping the JOIN and the ON varaibles in your upper lines for clarity as early as possible. This URL - https://www.w3schools.com/sql/sql_join_left.asp - describes JOIN in left table and right table terms. My humble concept is to JOIN driving to driven table. Your JOIN and WHERE code might be quicker if you switch variables to driving and driven table concept. — Wilson Hauck, May 19 '22 at 23:00
@WilsonHauck Thanks for your time. I will consider it. I have updated my question with some thoughts about a possible solution: the Query Cache. Do you suggest me to activate it on my system, or an upgrade is needed? The edit i've added to the question is at the beginning. — developpper, May 19 '22 at 23:57
Query Cache was REMOVED from MySQL version 8.0.nn and forward. That should tell you to AVOID the implementation. If a system is busy, and ROW change caused all queries that use the MODIFIED table to be kicked out of the Query Cache. Why pay the overhead, just get your queries running as fast as possible.. eversql.com might be able to help you. They will analyze ONE query, if you will create an account. — Wilson Hauck, May 20 '22 at 00:49
The is a possibility for some reason your tables indexes are out of date. ANALYZE TABLE eachtablename, separated. Would quickly ensure your indexes are current. Then retest and time your query execution time. — Wilson Hauck, May 20 '22 at 00:52
Additional information request, please. Any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; for server workload tuning analysis to provide suggestions. — Wilson Hauck, May 20 '22 at 00:59

O. Jones · Accepted Answer · 2022-05-20T12:37:26.010

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.

score 0 · Answer 2 · answered May 21 '22 at 05:44

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.

Why is this SQL query extremely slow?

2 Answers2