MySQL Bugs: #75807: Slow Query in MySQL 5.6

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

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)

Cleaned Up SQL Dump

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

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)

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)

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?

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);

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.

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