0

I have the following query;

SELECT `e`.`entity_id` FROM `catalog_category_entity` AS `e`
 LEFT JOIN `catalog_category_entity_varchar` AS `at_url_path_default` ON (`at_url_path_default`.`row_id` = `e`.`row_id`) AND (`at_url_path_default`.`attribute_id` = '125')
 LEFT JOIN `catalog_category_entity_varchar` AS `at_url_path` ON (`at_url_path`.`row_id` = `e`.`row_id`) AND (`at_url_path`.`attribute_id` = '125')
 LEFT JOIN `catalog_category_entity_int` AS `at_is_active_default` ON (`at_is_active_default`.`row_id` = `e`.`row_id`) AND (`at_is_active_default`.`attribute_id` = '46') AND `at_is_active_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_is_active` ON (`at_is_active`.`row_id` = `e`.`row_id`) AND (`at_is_active`.`attribute_id` = '46') AND (`at_is_active`.`store_id` = 1)
WHERE (((IF(at_url_path.value_id > 0, at_url_path.value, at_url_path_default.value) = 'flower')) AND ((IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) = 1)) AND (((`e`.`path` = '1/2') OR (`e`.`path` LIKE '1/2/%')))) AND (e.created_in <= '1689640860') AND (e.updated_in > '1689640860') GROUP BY `e`.`entity_id` ORDER BY `e`.`position` ASC, `e`.`entity_id` ASC
 LIMIT 20;

Each time it runs, it takes at minimum four to six seconds, which is not ideal for a high throughput system I am working on (Magento 2 with very high volume). When we turned off the query cache, this query came in and caused consistent stalls. Forced us to revert the change (which I really didn't like). But this was running for thirty plus seconds nearly each time so there wasn't any choice.

Here's the explain plan;

+----+-------------+----------------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+---------+----------------------------------------------+------+----------+----------------------------------------------+
| id | select_type | table                | partitions | type   | possible_keys                                                                                                                                                             | key                                                          | key_len | ref                                          | rows | filtered | Extra                                        |
+----+-------------+----------------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+---------+----------------------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | e                    | NULL       | ALL    | CATALOG_CATEGORY_ENTITY_PATH,CATALOG_CATEGORY_ENTITY_ENTITY_ID,CATALOG_CATEGORY_ENTITY_CREATED_IN,CATALOG_CATEGORY_ENTITY_UPDATED_IN                                      | NULL                                                         | NULL    | NULL                                         |  102 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | at_url_path_default  | NULL       | ref    | CATALOG_CATEGORY_ENTITY_VARCHAR_ROW_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_CATEGORY_ENTITY_VARCHAR_ATTRIBUTE_ID,CATALOG_CATEGORY_ENTITY_VARCHAR_ROW_ID                          | CATALOG_CATEGORY_ENTITY_VARCHAR_ROW_ID_ATTRIBUTE_ID_STORE_ID | 6       | e.row_id,const                               |   15 |   100.00 | NULL                                         |
|  1 | SIMPLE      | at_url_path          | NULL       | ref    | CATALOG_CATEGORY_ENTITY_VARCHAR_ROW_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_CATEGORY_ENTITY_VARCHAR_ATTRIBUTE_ID,CATALOG_CATEGORY_ENTITY_VARCHAR_ROW_ID                          | CATALOG_CATEGORY_ENTITY_VARCHAR_ROW_ID_ATTRIBUTE_ID_STORE_ID | 6       | e.row_id,const                               |   15 |   100.00 | Using where                                  |
|  1 | SIMPLE      | at_is_active_default | NULL       | eq_ref | CATALOG_CATEGORY_ENTITY_INT_ROW_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_CATEGORY_ENTITY_INT_ATTRIBUTE_ID,CATALOG_CATEGORY_ENTITY_INT_STORE_ID,CATALOG_CATEGORY_ENTITY_INT_ROW_ID | CATALOG_CATEGORY_ENTITY_INT_ROW_ID_ATTRIBUTE_ID_STORE_ID     | 8       | e.row_id,const,const                         |    1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | at_is_active         | NULL       | eq_ref | CATALOG_CATEGORY_ENTITY_INT_ROW_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_CATEGORY_ENTITY_INT_ATTRIBUTE_ID,CATALOG_CATEGORY_ENTITY_INT_STORE_ID,CATALOG_CATEGORY_ENTITY_INT_ROW_ID | CATALOG_CATEGORY_ENTITY_INT_ROW_ID_ATTRIBUTE_ID_STORE_ID     | 8       | e.row_id,const,const                         |    1 |   100.00 | Using where                                  |
+----+-------------+----------------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+---------+----------------------------------------------+------+----------+----------------------------------------------+

There is a full table scan that's consistently churning out millions of rows, explaining why it takes so long every time it runs. Even on low volume environments, it still takes four to five seconds.

Explain plan for just using path on catalog_category_entity with no joins;

mysql> EXPLAIN SELECT * FROM catalog_category_entity WHERE ((path = '1/2') OR (path LIKE '1/2/%')) AND (created_in <= '1689640860') AND (updated_in > '1689640860');
+----+-------------+-------------------------+------------+------+----------------------------------------------------------------------------------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table                   | partitions | type | possible_keys                                                                                      | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------------------+------------+------+----------------------------------------------------------------------------------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | catalog_category_entity | NULL       | ALL  | CATALOG_CATEGORY_ENTITY_PATH,CATALOG_CATEGORY_ENTITY_CREATED_IN,CATALOG_CATEGORY_ENTITY_UPDATED_IN | NULL | NULL    | NULL |  102 |   100.00 | Using where |
+----+-------------+-------------------------+------------+------+----------------------------------------------------------------------------------------------------+------+---------+------+------+----------+-------------+

That doesn't use the index either.

I have determined that it is the LIKE in the mix on path from catalog_category_entity that causes the issue. If I remove that, results are instant. Relatively speaking.

This is the standard create table output for all tables in the query;

catalog_category_entity | CREATE TABLE `catalog_category_entity` (
  `entity_id` int(10) unsigned NOT NULL COMMENT 'Entity Id',
  `attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Set ID',
  `parent_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Parent Category ID',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation Time',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
  `path` varchar(255) NOT NULL COMMENT 'Tree Path',
  `position` int(11) NOT NULL COMMENT 'Position',
  `level` int(11) NOT NULL DEFAULT '0' COMMENT 'Tree Level',
  `children_count` int(11) NOT NULL COMMENT 'Child Count',
  `row_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Version Id',
  `created_in` bigint(20) unsigned NOT NULL DEFAULT '1' COMMENT 'Update Id',
  `updated_in` bigint(20) unsigned NOT NULL DEFAULT '2147483647' COMMENT 'Next Update Id',
  PRIMARY KEY (`row_id`),
  KEY `CATALOG_CATEGORY_ENTITY_LEVEL` (`level`),
  KEY `CATALOG_CATEGORY_ENTITY_PATH` (`path`),
  KEY `CATALOG_CATEGORY_ENTITY_ENTITY_ID` (`entity_id`),
  KEY `CATALOG_CATEGORY_ENTITY_CREATED_IN` (`created_in`),
  KEY `CATALOG_CATEGORY_ENTITY_UPDATED_IN` (`updated_in`),
  CONSTRAINT `CAT_CTGR_ENTT_ENTT_ID_SEQUENCE_CAT_CTGR_SEQUENCE_VAL` FOREIGN KEY (`entity_id`) REFERENCES `sequence_catalog_category` (`sequence_value`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=225 DEFAULT CHARSET=utf8 COMMENT='Catalog Category Table'
catalog_category_entity_int | CREATE TABLE `catalog_category_entity_int` (
  `value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
  `attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute ID',
  `store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store ID',
  `value` int(11) DEFAULT NULL COMMENT 'Value',
  `row_id` int(10) unsigned NOT NULL COMMENT 'Version Id',
  PRIMARY KEY (`value_id`),
  UNIQUE KEY `CATALOG_CATEGORY_ENTITY_INT_ROW_ID_ATTRIBUTE_ID_STORE_ID` (`row_id`,`attribute_id`,`store_id`),
  KEY `CATALOG_CATEGORY_ENTITY_INT_ATTRIBUTE_ID` (`attribute_id`),
  KEY `CATALOG_CATEGORY_ENTITY_INT_STORE_ID` (`store_id`),
  KEY `CATALOG_CATEGORY_ENTITY_INT_ROW_ID` (`row_id`),
  CONSTRAINT `CATALOG_CATEGORY_ENTITY_INT_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE,
  CONSTRAINT `CAT_CTGR_ENTT_INT_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE,
  CONSTRAINT `CAT_CTGR_ENTT_INT_ROW_ID_CAT_CTGR_ENTT_ROW_ID` FOREIGN KEY (`row_id`) REFERENCES `catalog_category_entity` (`row_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6765 DEFAULT CHARSET=utf8 COMMENT='Catalog Category Integer Attribute Backend Table'
catalog_category_entity_varchar | CREATE TABLE `catalog_category_entity_varchar` (
  `value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
  `attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute ID',
  `store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store ID',
  `value` varchar(255) DEFAULT NULL COMMENT 'Value',
  `row_id` int(10) unsigned NOT NULL COMMENT 'Version Id',
  PRIMARY KEY (`value_id`),
  UNIQUE KEY `CATALOG_CATEGORY_ENTITY_VARCHAR_ROW_ID_ATTRIBUTE_ID_STORE_ID` (`row_id`,`attribute_id`,`store_id`),
  KEY `CATALOG_CATEGORY_ENTITY_VARCHAR_ATTRIBUTE_ID` (`attribute_id`),
  KEY `CATALOG_CATEGORY_ENTITY_VARCHAR_STORE_ID` (`store_id`),
  KEY `CATALOG_CATEGORY_ENTITY_VARCHAR_ROW_ID` (`row_id`),
  CONSTRAINT `CATALOG_CATEGORY_ENTITY_VARCHAR_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE,
  CONSTRAINT `CAT_CTGR_ENTT_VCHR_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE,
  CONSTRAINT `CAT_CTGR_ENTT_VCHR_ROW_ID_CAT_CTGR_ENTT_ROW_ID` FOREIGN KEY (`row_id`) REFERENCES `catalog_category_entity` (`row_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=87581 DEFAULT CHARSET=utf8 COMMENT='Catalog Category Varchar Attribute Backend Table'

path is indexed, but it's a normal index, rather than fulltext. My first thought was to drop the previous index and recreate it as fulltext.

This is what it looks on the dev environment with the change;

catalog_category_entity | CREATE TABLE `catalog_category_entity` (
  `entity_id` int unsigned NOT NULL COMMENT 'Entity Id',
  `attribute_set_id` smallint unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Set ID',
  `parent_id` int unsigned NOT NULL DEFAULT '0' COMMENT 'Parent Category ID',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation Time',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
  `path` varchar(255) NOT NULL COMMENT 'Tree Path',
  `position` int NOT NULL COMMENT 'Position',
  `level` int NOT NULL DEFAULT '0' COMMENT 'Tree Level',
  `children_count` int NOT NULL COMMENT 'Child Count',
  `row_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Version Id',
  `created_in` bigint unsigned NOT NULL DEFAULT '1' COMMENT 'Update Id',
  `updated_in` bigint unsigned NOT NULL DEFAULT '2147483647' COMMENT 'Next Update Id',
  PRIMARY KEY (`row_id`),
  KEY `CATALOG_CATEGORY_ENTITY_LEVEL` (`level`),
  KEY `CATALOG_CATEGORY_ENTITY_ENTITY_ID` (`entity_id`),
  KEY `CATALOG_CATEGORY_ENTITY_CREATED_IN` (`created_in`),
  KEY `CATALOG_CATEGORY_ENTITY_UPDATED_IN` (`updated_in`),
  FULLTEXT KEY `CATALOG_CATEGORY_ENTITY_PATH` (`path`),
  CONSTRAINT `CAT_CTGR_ENTT_ENTT_ID_SEQUENCE_CAT_CTGR_SEQUENCE_VAL` FOREIGN KEY (`entity_id`) REFERENCES `sequence_catalog_category` (`sequence_value`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=177 DEFAULT CHARSET=utf8mb3 COMMENT='Catalog Category Table'

However, when I try to use FIND_IN_SET() or MATCH() AGAINST(), it doesn't seem to work quire right. I was able to use REGEXP to get the same results, but that still took four or so seconds. I assume because the text is something like 1/2 or 1/2/5 that a fulltext key doesn't work as expected. These vales are category trees so that's why it looks the way it does.

Here's what the index stats look like;

mysql> show indexes from catalog_category_entity;
+-------------------------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                   | Non_unique | Key_name                           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| catalog_category_entity |          0 | PRIMARY                            |            1 | row_id      | A         |         102 |     NULL | NULL   |      | BTREE      |         |               |
| catalog_category_entity |          1 | CATALOG_CATEGORY_ENTITY_LEVEL      |            1 | level       | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| catalog_category_entity |          1 | CATALOG_CATEGORY_ENTITY_PATH       |            1 | path        | A         |         102 |     NULL | NULL   |      | BTREE      |         |               |
| catalog_category_entity |          1 | CATALOG_CATEGORY_ENTITY_ENTITY_ID  |            1 | entity_id   | A         |         102 |     NULL | NULL   |      | BTREE      |         |               |
| catalog_category_entity |          1 | CATALOG_CATEGORY_ENTITY_CREATED_IN |            1 | created_in  | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| catalog_category_entity |          1 | CATALOG_CATEGORY_ENTITY_UPDATED_IN |            1 | updated_in  | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.01 sec)

SQL mode;

mysql> show global variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

optimizer_switch;

mysql> show global variables like 'optimizer_switch';
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name    | Value                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,hash_join=off,hash_join_cost_based=on |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

How can I index the category tree data in such a way that it is searched, more or less, as it's entered to improve query performance? I have researched for special characters and if my understanding is correct, they're not indexed or taken into account. What alternatives do I have to help this query's performance?

[edit] Added index stats and sql_mode.
[edit2] Added explain plan for additional query and create table statements for joined tables.
[edit3] Added optimizer_switch output.

11
  • The way you use LIKE, it can use an index. Instead of an index on Path alone, what if you try an index combining path and updated_in together (in that order)? Jan 19 at 18:40
  • I'm not sure that would actually work given how Magento works. path is just a text representation of the category tree and updated_in doesn't correlate to the path update or anything. If that's irrelevant, I can try a covering index. I tried and it didn't actually change anything.
    – Marisa
    Jan 19 at 18:50
  • Your WHERE close has search criterias for both columns. Assuming a minority of records matching the path criteria would also meet the updated_in criteria could help to reduce the number of lookups to the table and make the use of the index more appealing for the query optimizer. It may not work, but it's easy to try Jan 19 at 18:59
  • 1
    You should definitely be using BTREE, not FULLTEXT, for this use case. Which of the three criteria is most selective (created_in, updated_in or path)? Please add stats to your question. What is the impact if you force the use of the CATALOG_CATEGORY_ENTITY_PATH index? e.position is not functionally dependent on e.entity_id so this query should be failing with an error, unless you have ONLY_FULL_GROUP_BY disabled. Jan 19 at 19:10
  • @user1191247 There is no sql_mode so the query works fine, short of the performance. I forced that index, but it returned with the same results. None of the three columns are particularly selective since they're not particularly unique to any given row. path is a category tree, updated_in and created_in are related to Magento 2's staged content feature so all of those fields are required as part of the functionality this comes from.
    – Marisa
    Jan 19 at 21:49

1 Answer 1

0
  • Those seem to be INNER JOINs, not LEFT JOINs.

  • AND (((e.path = '1/2') OR (e.path LIKE '1/2/%'))) --->

    AND e.path LIKE '1/2%' AND e.path REGEXP '^1/2($|/)'
    

    The trick here is to avoid OR by using the sargable e.path LIKE '1/2%' AND ...

  • If possible, make the GROUP BY and ORDER BY match; this will prevent a sort pass.

    GROUP BY e.position,     e.entity_id
    ORDER BY e.position ASC, e.entity_id ASC
    
  • Indexes:

    e:  INDEX(path, created_in)  (This may or may not help any.)
    at_url_path_default:  PRIMARY KEY(row_id,  attribute_id)
    at_url_path:          PRIMARY KEY(row_id,  attribute_id, value_id)
    at_is_active_default:  PRIMARY KEY(row_id, store_id, attribute_id)
    at_is_active:          PRIMARY KEY(row_id, store_id, attribute_id, value_id)
    
  • I suggest PRIMARY KEY above, but let me see SHOW CREATE TABLE to get a better handle on what is going on. Let me see the Create for e, too.

  • What are the possible values of value_id? If it is only 0 or 1, then there may be other tricks to pull.

  • Is this an "Entity-Attribute-Value" schema pattern?

1
  • Yes. Magento uses the EAV schema. I'll include the create table statements for the other two tables (they're referenced twice each). "e" is catalog_category_entity. value_id is a primary key for the catalog_category_entity_int and catalog_category_entity_varchar tables.
    – Marisa
    Jan 22 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.