Bug #75807 Slow Query in MySQL 5.6
Submitted: 6 Feb 2015 10:00 Modified: 18 Feb 2015 11:22
Reporter: Carsten Bohuslav Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S5 (Performance)
Version:5.6.22 OS:Linux
Assigned to: CPU Architecture:Any

[6 Feb 2015 10:00] Carsten Bohuslav
Description:
The following Query got a significantly diffrent runtime between MySQL 5.5 and 5.6. 

The runtime under 5.5 is around 6-8 Seconds and on 5.6 it got a runtime of 180 Seconds on our Systems. I have test this under MariaDB 10.0.16 the time there is 0,5 Seconds.  We are using Debian as Operating System. 

Query:
SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, 
ccp.position + 10000 AS `position`, 0 AS `is_parent`, 23 AS `store_id`, 
IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM 
`catalog_category_entity` AS `cc` 
INNER JOIN `catalog_category_entity` AS `cc2` ON cc2.path LIKE 
CONCAT(`cc`.`path`, '/%') AND cc.entity_id NOT IN (1) 
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = 
cc2.entity_id 
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = 
ccp.product_id 
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.entity_id = 
ccp.product_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 96 
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.entity_id = 
ccp.product_id AND cpss.attribute_id = cpsd.attribute_id AND 
cpss.store_id = 23 
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.entity_id = 
ccp.product_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 102 
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.entity_id = 
ccp.product_id AND cpvs.attribute_id = cpvd.attribute_id AND 
cpvs.store_id = 23 
INNER JOIN `catalog_category_entity_int` AS `ccad` ON ccad.entity_id = 
cc.entity_id AND ccad.store_id = 0 AND ccad.attribute_id = 51 
LEFT JOIN `catalog_category_entity_int` AS `ccas` ON ccas.entity_id = 
cc.entity_id AND ccas.attribute_id = ccad.attribute_id AND ccas.store_id 
= 23 WHERE (cpw.website_id = '17') AND (IFNULL(cpss.value, cpsd.value) = 
1) AND (IFNULL(cpvs.value, cpvd.value) IN (4, 2)) AND 
(IFNULL(ccas.value, ccad.value) = 1) AND (`cc`.`entity_id` >= 1501) AND 
(`cc`.`entity_id` < 2001)

How to repeat:
Import the SQL Dump attached and run the following Query

SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, 
ccp.position + 10000 AS `position`, 0 AS `is_parent`, 23 AS `store_id`, 
IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM 
`catalog_category_entity` AS `cc` 
INNER JOIN `catalog_category_entity` AS `cc2` ON cc2.path LIKE 
CONCAT(`cc`.`path`, '/%') AND cc.entity_id NOT IN (1) 
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = 
cc2.entity_id 
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = 
ccp.product_id 
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.entity_id = 
ccp.product_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 96 
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.entity_id = 
ccp.product_id AND cpss.attribute_id = cpsd.attribute_id AND 
cpss.store_id = 23 
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.entity_id = 
ccp.product_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 102 
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.entity_id = 
ccp.product_id AND cpvs.attribute_id = cpvd.attribute_id AND 
cpvs.store_id = 23 
INNER JOIN `catalog_category_entity_int` AS `ccad` ON ccad.entity_id = 
cc.entity_id AND ccad.store_id = 0 AND ccad.attribute_id = 51 
LEFT JOIN `catalog_category_entity_int` AS `ccas` ON ccas.entity_id = 
cc.entity_id AND ccas.attribute_id = ccad.attribute_id AND ccas.store_id 
= 23 WHERE (cpw.website_id = '17') AND (IFNULL(cpss.value, cpsd.value) = 
1) AND (IFNULL(cpvs.value, cpvd.value) IN (4, 2)) AND 
(IFNULL(ccas.value, ccad.value) = 1) AND (`cc`.`entity_id` >= 1501) AND 
(`cc`.`entity_id` < 2001)
[6 Feb 2015 10:03] Carsten Bohuslav
Cleaned Up SQL Dump

Attachment: Structure-06.02.15.sql.gz (application/x-gzip, text), 2.86 MiB.

[6 Feb 2015 10:48] Miguel Solorzano
Thank you for the bug report.

|        1627 |     116654 |    10001 |         0 |       23 |          4 |
|        1627 |     123562 |    10001 |         0 |       23 |          4 |
|        1627 |     124376 |    10001 |         0 |       23 |          4 |
|        1627 |     124629 |    10001 |         0 |       23 |          4 |
+-------------+------------+----------+-----------+----------+------------+
142 rows in set (1 min 4.19 sec)

mysql 5.6 > show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.6.24              |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.6.24              |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
7 rows in set (0.00 sec)
-------------------------------------------------------------------------------
|        1627 |     109492 |    10000 |         0 |       23 |          4 |
|        1627 |     116650 |    10001 |         0 |       23 |          4 |
|        1627 |     116651 |    10001 |         0 |       23 |          4 |
|        1627 |     116652 |    10001 |         0 |       23 |          4 |
|        1627 |     116653 |    10001 |         0 |       23 |          4 |
|        1627 |     116654 |    10001 |         0 |       23 |          4 |
+-------------+------------+----------+-----------+----------+------------+
142 rows in set (4.14 sec)

mysql 5.5 > show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.5.43              |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.5.43-log          |
| version_comment         | Source distribution |
| version_compile_machine | AMD64               |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
7 rows in set (0.00 sec)
[6 Feb 2015 11:16] Miguel Solorzano
Server 5.7  not affected even faster than 5.5:

|        1542 |     107512 |    10000 |         0 |       23 |          4 |
|        1542 |     113990 |    10000 |         0 |       23 |          4 |
|        1542 |     113993 |    10000 |         0 |       23 |          4 |
|        1546 |     111607 |    10000 |         0 |       23 |          4 |
|        1546 |     112863 |    10000 |         0 |       23 |          4 |
+-------------+------------+----------+-----------+----------+------------+
142 rows in set (0.22 sec)

mysql 5.7 > show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.7.6               |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.7.6-m16           |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
7 rows in set (0.00 sec)
[18 Feb 2015 8:57] Carsten Bohuslav
So what do you recommend as Solution the Servers with the affected MySQL Version are live ones and we cant switch to a not stable Version (5.7). And the performance hit of the Bug is a big one for our Servers. 

What alternatives do you got for Us?
[18 Feb 2015 10:57] Øystein Grøvlen
You can rewrite your query to use hints to force a particular query plan.  To force a specific join order, use STRAIGHT_JOIN, and list tables in the order you want to join them.  E.g., the following query with STRAIGHT_JOIN gives the same query plan in MySQL 5.6 as I get in MySQL 5.7 without hints:

explain SELECT STRAIGHT_JOIN `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, ccp.position + 10000 AS `position`, 0 AS `is_parent`, 23 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` 
FROM `catalog_category_entity_int` AS `ccad` 
INNER JOIN `catalog_category_entity` AS `cc` 
        ON ccad.entity_id = cc.entity_id 
LEFT JOIN `catalog_category_entity_int` AS `ccas` 
        ON ccas.entity_id = cc.entity_id AND 
           ccas.attribute_id = ccad.attribute_id AND ccas.store_id = 23 
INNER JOIN `catalog_category_entity` AS `cc2` 
        ON cc2.path LIKE CONCAT(`cc`.`path`, '/%') AND cc.entity_id NOT IN (1) 
INNER JOIN `catalog_category_product` AS `ccp` 
        ON ccp.category_id = cc2.entity_id 
INNER JOIN `catalog_product_website` AS `cpw` 
        ON cpw.product_id = ccp.product_id 
INNER JOIN `catalog_product_entity_int` AS `cpvd` 
        ON cpvd.entity_id = ccp.product_id AND 
           cpvd.store_id = 0 AND cpvd.attribute_id = 102 
INNER JOIN `catalog_product_entity_int` AS `cpsd` 
        ON cpsd.entity_id = ccp.product_id AND 
           cpsd.store_id = 0 AND cpsd.attribute_id = 96 
LEFT JOIN `catalog_product_entity_int` AS `cpss` 
        ON cpss.entity_id = ccp.product_id AND 
           cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 23 
LEFT JOIN `catalog_product_entity_int` AS `cpvs` 
        ON cpvs.entity_id = ccp.product_id AND 
           cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 23 
WHERE (cpw.website_id = '17') 
  AND (IFNULL(cpss.value, cpsd.value) = 1)
  AND (IFNULL(cpvs.value, cpvd.value) IN (4, 2))
  AND (IFNULL(ccas.value, ccad.value) = 1) 
  AND ccad.store_id = 0 AND ccad.attribute_id = 51 
  AND (`cc`.`entity_id` >= 1501) 
  AND (`cc`.`entity_id` < 2001);
[18 Feb 2015 11:22] Carsten Bohuslav
We cant rewrite the Query because it is generated from the Shopsystem Magento, that would need touching the corecode base of Magento.  

Are any other options like optimizer switches we could try as a workaround.
[20 Feb 2015 9:54] Vorname Nachname
We're having big issues, too. A select statement runs painfully slow after migration from 5.5.x to 5.6.22. Is this bug going to be fixed in 5.6.24? http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-24.html