Details
-
Bug
-
Resolution: Fixed
-
High
-
2.3.1
-
None
Description
The customer reported very long time needed to reindex search engine.
The problem is caused by missing index in ezpage_map_blocks_zones.
Query
SELECT a.id, a.name, a.value, mab.block_id, mbz.zone_id FROM `ezpage_attributes` a LEFT JOIN `ezpage_map_attributes_blocks` mab ON mab.attribute_id = a.id LEFT JOIN `ezpage_map_blocks_zones` mbz ON mab.block_id = mbz.block_id LEFT JOIN `ezpage_map_zones_pages` mzp ON mbz.zone_id = mzp.zone_id WHERE mzp.page_id = 4;
EXPLAIN without index
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------+-------+----------+----------------------------------------------------+
| 1 | SIMPLE | a | NULL | ALL | PRIMARY | NULL | NULL | NULL | 14118 | 100.00 | NULL |
| 1 | SIMPLE | mab | NULL | ref | PRIMARY | PRIMARY | 4 | cs7096.a.id | 1 | 100.00 | Using where; Using index |
| 1 | SIMPLE | mbz | NULL | ALL | NULL | NULL | NULL | NULL | 15935 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | mzp | NULL | eq_ref | PRIMARY | PRIMARY | 8 | cs7096.mbz.zone_id,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------+-------+----------+----------------------------------------------------+
EXPLAIN with index
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------+-------+----------+--------------------------+
| 1 | SIMPLE | a | NULL | ALL | PRIMARY | NULL | NULL | NULL | 14118 | 100.00 | NULL |
| 1 | SIMPLE | mab | NULL | ref | PRIMARY | PRIMARY | 4 | cs7096.a.id | 1 | 100.00 | Using where; Using index |
| 1 | SIMPLE | mbz | NULL | ref | idx | idx | 4 | cs7096.mab.block_id | 1 | 100.00 | Using index |
| 1 | SIMPLE | mzp | NULL | eq_ref | PRIMARY | PRIMARY | 8 | cs7096.mbz.zone_id,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------+-------+----------+--------------------------+
After adding the new index, reindex time on the customer's database takes ~10 minutes instead of ~3 hours.