Question 26822 in CentOS Question posted in
The official documentation can be found here.

Why is this SQL query extremely slow? – CentOS

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
Tags:

2

Answers


  1. 0

    • 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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search