|
Composite unique indexes aren't used when they are available. A query from real world application
(Magento 2.4.3) takes almost 2 minutes vs 0 seconds when correct indexes are used.
A database dump with data is attached.
Sample query below should use CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID key for at_price table but it doesn't.
Results from MariaDB 10.4.22 on CentOS 7. I didn't test it with newer versions. MariaDB 10.4 is recommended for Magento 2.4.3:
https://devdocs.magento.com/guides/v2.4/install-gde/system-requirements.html
EXPLAIN EXTENDED SELECT at_tax_class_id.value_id, at_price.value_id
|
FROM `catalog_product_entity` AS `e`
|
LEFT JOIN `catalog_product_entity_int` AS `at_tax_class_id` ON (`at_tax_class_id`.`entity_id` = `e`.`entity_id`) AND (`at_tax_class_id`.`attribute_id` = 115) AND (`at_tax_class_id`.`store_id` = 1)
|
LEFT JOIN `catalog_product_entity_decimal` AS `at_price` ON (`at_price`.`entity_id` = `e`.`entity_id`) AND (`at_price`.`attribute_id` = 69) AND (`at_price`.`store_id` = 0)
|
LIMIT 20;
|
Results from 10.4.22-MariaDB on CentOS 7:
+------+-------------+-----------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------------------------------+-------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-----------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------------------------------+-------+----------+-------------+
|
| 1 | SIMPLE | e | index | NULL | CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID | 2 | NULL | 11887 | 100.00 | Using index |
|
| 1 | SIMPLE | at_tax_class_id | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 0.02 | Using index |
|
| 1 | SIMPLE | at_price | ref | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID | CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID | 2 | const | 12108 | 50.00 | Using where |
|
+------+-------------+-----------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------------------------------+-------+----------+-------------+
|
Real world query from Magento 2. Sample database with data attached:
EXPLAIN EXTENDED
|
SELECT `e`.*, IF(at_tax_class_id.value_id > 0, at_tax_class_id.value, at_tax_class_id_default.value) AS `tax_class_id`,
|
`at_special_price`.`value` AS `special_price`,
|
IF(at_special_from_date.value_id > 0, at_special_from_date.value, at_special_from_date_default.value) AS `special_from_date`,
|
IF(at_special_to_date.value_id > 0, at_special_to_date.value, at_special_to_date_default.value) AS `special_to_date`,
|
`at_price`.`value` AS `price`,
|
`at_cost`.`value` AS `cost`,
|
IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) AS `visibility`
|
FROM `catalog_product_entity` AS `e`
|
LEFT JOIN `catalog_product_entity_int` AS `at_tax_class_id_default` ON (`at_tax_class_id_default`.`entity_id` = `e`.`entity_id`) AND (`at_tax_class_id_default`.`attribute_id` = '115') AND `at_tax_class_id_default`.`store_id` = 0
|
LEFT JOIN `catalog_product_entity_int` AS `at_tax_class_id` ON (`at_tax_class_id`.`entity_id` = `e`.`entity_id`) AND (`at_tax_class_id`.`attribute_id` = '115') AND (`at_tax_class_id`.`store_id` = 1)
|
LEFT JOIN `catalog_product_entity_decimal` AS `at_special_price` ON (`at_special_price`.`entity_id` = `e`.`entity_id`) AND (`at_special_price`.`attribute_id` = '70') AND (`at_special_price`.`store_id` = 0)
|
LEFT JOIN `catalog_product_entity_datetime` AS `at_special_from_date_default` ON (`at_special_from_date_default`.`entity_id` = `e`.`entity_id`) AND (`at_special_from_date_default`.`attribute_id` = '71') AND `at_special_from_date_default`.`store_id` = 0
|
LEFT JOIN `catalog_product_entity_datetime` AS `at_special_from_date` ON (`at_special_from_date`.`entity_id` = `e`.`entity_id`) AND (`at_special_from_date`.`attribute_id` = '71') AND (`at_special_from_date`.`store_id` = 1)
|
LEFT JOIN `catalog_product_entity_datetime` AS `at_special_to_date_default` ON (`at_special_to_date_default`.`entity_id` = `e`.`entity_id`) AND (`at_special_to_date_default`.`attribute_id` = '72') AND `at_special_to_date_default`.`store_id` = 0
|
LEFT JOIN `catalog_product_entity_datetime` AS `at_special_to_date` ON (`at_special_to_date`.`entity_id` = `e`.`entity_id`) AND (`at_special_to_date`.`attribute_id` = '72') AND (`at_special_to_date`.`store_id` = 1)
|
LEFT JOIN `catalog_product_entity_decimal` AS `at_price` ON (`at_price`.`entity_id` = `e`.`entity_id`) AND (`at_price`.`attribute_id` = '69') AND (`at_price`.`store_id` = 0)
|
LEFT JOIN `catalog_product_entity_decimal` AS `at_cost` ON (`at_cost`.`entity_id` = `e`.`entity_id`) AND (`at_cost`.`attribute_id` = '73') AND (`at_cost`.`store_id` = 0)
|
LEFT JOIN `catalog_product_entity_int` AS `at_visibility_default` ON (`at_visibility_default`.`entity_id` = `e`.`entity_id`) AND (`at_visibility_default`.`attribute_id` = '95') AND `at_visibility_default`.`store_id` = 0
|
LEFT JOIN `catalog_product_entity_int` AS `at_visibility` ON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND (`at_visibility`.`attribute_id` = '95') AND (`at_visibility`.`store_id` = 1)
|
ORDER BY e.entity_id ASC
|
LIMIT 20;
|
Rersults and execution time of the query from MariaDB 10.4.22 on CentOS 7. Wrong indexes used:
+------+-------------+------------------------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------+---------+------------------------------+-------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+------------------------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------+---------+------------------------------+-------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------+
|
| 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 11887 | 100.00 | Using temporary; Using filesort |
|
| 1 | SIMPLE | at_tax_class_id_default | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 5.41 | |
|
| 1 | SIMPLE | at_tax_class_id | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 0.02 | |
|
| 1 | SIMPLE | at_special_price | ref | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID | CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID | 2 | const | 9779 | 50.00 | Using where |
|
| 1 | SIMPLE | at_special_from_date_default | eq_ref | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 100.00 | |
|
| 1 | SIMPLE | at_special_from_date | ref | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID | CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID | 2 | const | 1 | 16.15 | Using where |
|
| 1 | SIMPLE | at_special_to_date_default | eq_ref | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 100.00 | |
|
| 1 | SIMPLE | at_special_to_date | ref | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID | CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID | 2 | const | 1 | 28.18 | Using where |
|
| 1 | SIMPLE | at_price | eq_ref | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 100.00 | |
|
| 1 | SIMPLE | at_cost | eq_ref | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 100.00 | |
|
| 1 | SIMPLE | at_visibility_default | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 4.88 | |
|
| 1 | SIMPLE | at_visibility | index_merge | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID | 2,2 | NULL | 27 | 100.00 | Using intersect(CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID); Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+------------------------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------+---------+------------------------------+-------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------+
|
Execution time when the query runs:
20 rows in set (1 min 52.189 sec)
|
Compared to rersults and execution time of the same query using the same data from 10.2.36-MariaDB SUSE package on openSUSE Leap 15.1 where correct indexes are used:
+------+-------------+------------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------+---------+------------------------------+-------+----------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+------------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------+---------+------------------------------+-------+----------+-------+
|
| 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 12109 | 100.00 | |
|
| 1 | SIMPLE | at_tax_class_id_default | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 100.00 | |
|
| 1 | SIMPLE | at_tax_class_id | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 100.00 | |
|
| 1 | SIMPLE | at_special_price | eq_ref | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 100.00 | |
|
| 1 | SIMPLE | at_special_from_date_default | eq_ref | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 100.00 | |
|
| 1 | SIMPLE | at_special_from_date | eq_ref | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 100.00 | |
|
| 1 | SIMPLE | at_special_to_date_default | eq_ref | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 100.00 | |
|
| 1 | SIMPLE | at_special_to_date | eq_ref | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 100.00 | |
|
| 1 | SIMPLE | at_price | eq_ref | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 100.00 | |
|
| 1 | SIMPLE | at_cost | eq_ref | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 100.00 | |
|
| 1 | SIMPLE | at_visibility_default | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 100.00 | |
|
| 1 | SIMPLE | at_visibility | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | aaa2.e.entity_id,const,const | 1 | 100.00 | |
|
+------+-------------+------------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------+---------+------------------------------+-------+----------+-------+
|
Execution time when the query runs:
20 rows in set (0.00 sec)
|
|
live_issue2.sql.zip