I’m on OpenMage LTS 20.0.6 installation and while the site doesn’t have much data, it keeps getting a lot of high execution queries that seem related to Layered Navigation
+----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name | value |
+----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Version | OpenMage LTS 20.0.6 |
| Edition | Community |
| Root | /httpdocs |
| Cache Backend | Cm_Cache_Backend_Redis |
| Session | db |
| Install Date | Sat, 25 Aug 2018 16:04:15 +0000 |
| Core Autoloader Overwrites | 28 |
| Vendors (core) | Mage, Zend |
| Vendors (community) | AddThis, Aoe, Clarion, Cleantalk, Cm, Fooman, Ioweb, Litespeed, MSP, Me, Nick, Nwdthemes, PH2M, Phoenix, Unstablenet, Valdecode, Varien, Wyomind, Yireo, Zend |
| Vendors (local) | Amasty, Anowave, Apache, Aschroder, Converge, Inchoo, Ioweb, Leonidas, Mage, Magegiant, Smartwave, Toogas, W2, Wyomind |
| Attribute Count | 256 |
| Customer Count | 9697 |
| Category Count | 229 |
| Product Count | 1822 |
+----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
MariaDB version
Server version: 10.6.17-MariaDB-1:10.6.17+maria~ubu2004-log mariadb.org binary distribution
The queries look like this
# Time: 240328 12:53:51
# User@Host: db_domain3[db_domain3] @ localhost [127.0.0.1]
# Thread_id: 2596391 Schema: db_domain3 QC_hit: No
# Query_time: 64.481879 Lock_time: 0.000145 Rows_sent: 0 Rows_examined: 10
# Rows_affected: 0 Bytes_sent: 138
# Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# Full_scan: No Full_join: No Tmp_table: Yes Tmp_table_on_disk: No
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0 Priority_queue: No
#
# explain: id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
# explain: 1 SIMPLE screen_resolution_idx ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE 4 const 1 1.00 100.00 100.00 Using where; Using index; Using temporary; Using filesort
# explain: 1 SIMPLE memory_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_domain3.screen_resolution_idx.entity_id,const,const,const 1 1.00 100.00 100.00 Using index
# explain: 1 SIMPLE primary_camera_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_domain3.screen_resolution_idx.entity_id,const,const,const 1 1.00 100.00 100.00 Using index
# explain: 1 SIMPLE manufacturer2_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_domain3.screen_resolution_idx.entity_id,const,const,const 1 1.00 100.00 100.00 Using index
# explain: 1 SIMPLE ram_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_domain3.screen_resolution_idx.entity_id,const,const,const 1 1.00 100.00 100.00 Using index
# explain: 1 SIMPLE cat_index eq_ref PRIMARY,IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,15D3C269665C74C2219037D534F4B0DC PRIMARY 10 const,db_domain3.screen_resolution_idx.entity_id,const 1 1.00 100.00 100.00 Using where
# explain: 1 SIMPLE nfcsosto_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_domain3.screen_resolution_idx.entity_id,const,const,const 1 1.00 100.00 100.00 Using index
# explain: 1 SIMPLE operating_system_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_domain3.screen_resolution_idx.entity_id,const,const,const 1 1.00 100.00 100.00 Using index
# explain: 1 SIMPLE gdiktia_idx ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,idx_attribute_store_value_entity IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID 8 db_domain3.screen_resolution_idx.entity_id,const,const 1 1.00 100.00 100.00 Using index
# explain: 1 SIMPLE price_index eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID,IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE PRIMARY 8 db_domain3.screen_resolution_idx.entity_id,const,const 1 1.00 100.00 0.00 Using where
# explain: 1 SIMPLE e eq_ref PRIMARY PRIMARY 4 db_domain3.screen_resolution_idx.entity_id 1 NULL 100.00 NULL Using index
# explain: 1 SIMPLE screen_size_idx range PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE 4 NULL 2 NULL 75.00 NULL Using where; Using index; Using join buffer (flat, BNL join)
#
SET timestamp=1711623231;
SELECT `gdiktia_idx`.`value`, COUNT(gdiktia_idx.entity_id) AS `count` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '15'
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
INNER JOIN `catalog_product_index_eav` AS `manufacturer2_idx` ON manufacturer2_idx.entity_id = e.entity_id AND manufacturer2_idx.attribute_id = '186' AND manufacturer2_idx.store_id = 1 AND manufacturer2_idx.value = '6'
INNER JOIN `catalog_product_index_eav` AS `screen_size_idx` ON screen_size_idx.entity_id = e.entity_id AND screen_size_idx.attribute_id = '187' AND screen_size_idx.store_id = 1 AND screen_size_idx.value = '307'
INNER JOIN `catalog_product_index_eav` AS `screen_resolution_idx` ON screen_resolution_idx.entity_id = e.entity_id AND screen_resolution_idx.attribute_id = '188' AND screen_resolution_idx.store_id = 1 AND screen_resolution_idx.value = '321'
INNER JOIN `catalog_product_index_eav` AS `operating_system_idx` ON operating_system_idx.entity_id = e.entity_id AND operating_system_idx.attribute_id = '195' AND operating_system_idx.store_id = 1 AND operating_system_idx.value = '48'
INNER JOIN `catalog_product_index_eav` AS `ram_idx` ON ram_idx.entity_id = e.entity_id AND ram_idx.attribute_id = '196' AND ram_idx.store_id = 1 AND ram_idx.value = '52'
INNER JOIN `catalog_product_index_eav` AS `memory_idx` ON memory_idx.entity_id = e.entity_id AND memory_idx.attribute_id = '197' AND memory_idx.store_id = 1 AND memory_idx.value = '61'
INNER JOIN `catalog_product_index_eav` AS `primary_camera_idx` ON primary_camera_idx.entity_id = e.entity_id AND primary_camera_idx.attribute_id = '198' AND primary_camera_idx.store_id = 1 AND primary_camera_idx.value = '310'
INNER JOIN `catalog_product_index_eav` AS `nfcsosto_idx` ON nfcsosto_idx.entity_id = e.entity_id AND nfcsosto_idx.attribute_id = '258' AND nfcsosto_idx.store_id = 1 AND nfcsosto_idx.value = '376'
INNER JOIN `catalog_product_index_eav` AS `gdiktia_idx` ON gdiktia_idx.entity_id = e.entity_id AND gdiktia_idx.attribute_id = '232' AND gdiktia_idx.store_id = '1' WHERE ((price_index.min_price -(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN 0.2400 ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN 0.2400 ELSE 0 END)+((price_index.min_price-(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN 0.2400 ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN 0.2400 ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN 0.2400 ELSE 0 END)) >= 149.995000) GROUP BY `gdiktia_idx`.`value`;
And by the Count statement I can assume they’re related to the total count of each attribute option value most likely due to layered navigation. The issue is that I have already disabled counting the items in the backend
Out of all my installations this is the only one causing this issue. I’m not sure how to tackle this, any ideas?