[MDEV-6735] Range checked for each record used with key - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5.39, 10.0.13
Fix Version/s: 10.0.20, 5.5.44
Component/s: Optimizer
Labels:
- optimizer

Sprint:
10.0.20

Description

Using the attached mysqldump, the following query is very slow in MariaDB compared to MySQL 5.5 or MySQL 5.6:

SELECT 1 FROM `catalog_category_flat_store_1` AS `main_table`
              LEFT JOIN `core_url_rewrite` AS `url_rewrite`
                 ON url_rewrite.category_id=main_table.entity_id
                 AND url_rewrite.is_system=1
                 AND url_rewrite.store_id = 1
                 AND url_rewrite.id_path LIKE 'category/%'
          WHERE (main_table.include_in_menu = '1')
          AND (main_table.is_active = '1')
          AND (main_table.path like '1/2/%')
          ORDER BY `main_table`.`position` ASC

The schema and query are part of Magento, but the attached dump has had FK, some unique indexes, and most of the columns dropped.

The query plan in MySQL, which executes quickly:

|  1 | SIMPLE      | main_table  | ALL   | IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH                                                                                                                       | NULL                                            | NULL    | NULL |  124 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | url_rewrite | range | UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID,IDX_CORE_URL_REWRITE_ID_PATH,IDX_CORE_URL_REWRITE_STORE_ID,FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID | UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID | 773     | NULL | 1138 | Using where                                  |

5.6 has BNL and using Join buffer for the second table, and runs at a similar speed.

In MariaDB 5.5 and MariaDB 10, the following slow plan is used:

|    1 | SIMPLE      | main_table  | ALL   | IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH                                                                                                                       | NULL                                            | NULL    | NULL |  149 | Using where; Using filesort                     |
|    1 | SIMPLE      | url_rewrite | range | UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID,IDX_CORE_URL_REWRITE_ID_PATH,IDX_CORE_URL_REWRITE_STORE_ID,FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID | UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID | 773     | NULL | 1138 | Range checked for each record (index map: 0x74) |

This can be made fast in MariaDB with ADD INDEX ix_foo (category_id, store_id) or IGNORE INDEX.

There also seems to be no optimizer_switch to turn off this strategy.

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

bugtest.sql.gz
8.09 MB
2014-09-12 02:43
mdev-6735_x2_x1_testcase.sql.gz
156 kB
2015-03-21 17:26
MDEV-6735.patch
0.6 kB
2015-03-20 13:39
mdev-6735.sql
5 kB
2015-03-20 17:34

Issue Links

relates to

MDEV-7786 regression in query plan - join buffer (flat, BNL join) -> Range checked for each record (index map: 0x6); Rowid-ordered scan

Closed

MDEV-7923 forcing index changes query plan (even if default chose that index)

Closed

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2014-09-15 14:56

5.5 revno 4278:
102 rows in set (7 min 45.45 sec)

|    1 | SIMPLE      | main_table  | ALL   | IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH                                                                                                                 | NULL                         | NULL    | NULL |  143 |    98.60 | Using where; Using filesort                     |
|    1 | SIMPLE      | url_rewrite | range | IDX_CORE_URL_REWRITE_TARGET_PATH_STORE_ID,IDX_CORE_URL_REWRITE_ID_PATH,IDX_CORE_URL_REWRITE_STORE_ID,FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID | IDX_CORE_URL_REWRITE_ID_PATH | 768     | NULL | 1138 |    75.04 | Range checked for each record (index map: 0x1E) |

select 1 AS `1` from `test`.`catalog_category_flat_store_1` `main_table` left join `test`.`core_url_rewrite` `url_rewrite` on(((`test`.`url_rewrite`.`category_id` = `test`.`main_table`.`entity_id`) and (`test`.`url_rewrite`.`is_system` = 1) and (`test`.`url_rewrite`.`store_id` = 1) and (`test`.`url_rewrite`.`id_path` like 'category/%'))) where ((`test`.`main_table`.`include_in_menu` = '1') and (`test`.`main_table`.`is_active` = '1') and (`test`.`main_table`.`path` like '1/2/%')) order by `test`.`main_table`.`position`

10.0 revno 4393:
102 rows in set (10 min 16.73 sec)

| id   | select_type | table       | type | possible_keys                                                                                                                                          | key                          | key_len | ref  | rows    | filtered | Extra                                           |
+------+-------------+-------------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+------+---------+----------+-------------------------------------------------+
|    1 | SIMPLE      | main_table  | ALL  | IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH                                                                                                                 | NULL                         | NULL    | NULL |     143 |    98.60 | Using where; Using filesort                     |
|    1 | SIMPLE      | url_rewrite | ALL  | IDX_CORE_URL_REWRITE_TARGET_PATH_STORE_ID,IDX_CORE_URL_REWRITE_ID_PATH,IDX_CORE_URL_REWRITE_STORE_ID,FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID | IDX_CORE_URL_REWRITE_ID_PATH | 768     | NULL | 1069786 |     0.08 | Range checked for each record (index map: 0x1E) |

Note  | 1003 | select 1 AS `1` from `test`.`catalog_category_flat_store_1` `main_table` left join `test`.`core_url_rewrite` `url_rewrite` on(((`test`.`url_rewrite`.`category_id` = `test`.`main_table`.`entity_id`) and (`test`.`url_rewrite`.`is_system` = 1) and (`test`.`url_rewrite`.`store_id` = 1) and (`test`.`url_rewrite`.`id_path` like 'category/%'))) where ((`test`.`main_table`.`include_in_menu` = '1') and (`test`.`main_table`.`is_active` = '1') and (`test`.`main_table`.`path` like '1/2/%')) order by `test`.`main_table`.`position`

5.6 revno 6060:
102 rows in set (0.13 sec)

|  1 | SIMPLE      | main_table  | ALL   | IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH                                                                                                                 | NULL                         | NULL    | NULL |  143 |    98.60 | Using where; Using temporary; Using filesort       |
|  1 | SIMPLE      | url_rewrite | range | IDX_CORE_URL_REWRITE_TARGET_PATH_STORE_ID,IDX_CORE_URL_REWRITE_ID_PATH,IDX_CORE_URL_REWRITE_STORE_ID,FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID | IDX_CORE_URL_REWRITE_ID_PATH | 768     | NULL | 1138 |   100.00 | Using where; Using join buffer (Block Nested Loop) |

/* select#1 */ select 1 AS `1` from `test`.`catalog_category_flat_store_1` `main_table` left join `test`.`core_url_rewrite` `url_rewrite` on(((`test`.`url_rewrite`.`store_id` = 1) and (`test`.`url_rewrite`.`is_system` = 1) and (`test`.`url_rewrite`.`category_id` = `test`.`main_table`.`entity_id`) and (`test`.`url_rewrite`.`id_path` like 'category/%'))) where ((`test`.`main_table`.`include_in_menu` = '1') and (`test`.`main_table`.`is_active` = '1') and (`test`.`main_table`.`path` like '1/2/%')) order by `test`.`main_table`.`position`

Show

Elena Stepanova added a comment - 2014-09-15 14:56 5.5 revno 4278: 102 rows in set (7 min 45.45 sec) | 1 | SIMPLE | main_table | ALL | IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH | NULL | NULL | NULL | 143 | 98.60 | Using where; Using filesort | | 1 | SIMPLE | url_rewrite | range | IDX_CORE_URL_REWRITE_TARGET_PATH_STORE_ID,IDX_CORE_URL_REWRITE_ID_PATH,IDX_CORE_URL_REWRITE_STORE_ID,FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID | IDX_CORE_URL_REWRITE_ID_PATH | 768 | NULL | 1138 | 75.04 | Range checked for each record (index map: 0x1E) | select 1 AS `1` from `test`.`catalog_category_flat_store_1` `main_table` left join `test`.`core_url_rewrite` `url_rewrite` on(((`test`.`url_rewrite`.`category_id` = `test`.`main_table`.`entity_id`) and (`test`.`url_rewrite`.`is_system` = 1) and (`test`.`url_rewrite`.`store_id` = 1) and (`test`.`url_rewrite`.`id_path` like 'category/%'))) where ((`test`.`main_table`.`include_in_menu` = '1') and (`test`.`main_table`.`is_active` = '1') and (`test`.`main_table`.`path` like '1/2/%')) order by `test`.`main_table`.`position` 10.0 revno 4393: 102 rows in set (10 min 16.73 sec) | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+------+---------+----------+-------------------------------------------------+ | 1 | SIMPLE | main_table | ALL | IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH | NULL | NULL | NULL | 143 | 98.60 | Using where; Using filesort | | 1 | SIMPLE | url_rewrite | ALL | IDX_CORE_URL_REWRITE_TARGET_PATH_STORE_ID,IDX_CORE_URL_REWRITE_ID_PATH,IDX_CORE_URL_REWRITE_STORE_ID,FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID | IDX_CORE_URL_REWRITE_ID_PATH | 768 | NULL | 1069786 | 0.08 | Range checked for each record (index map: 0x1E) | Note | 1003 | select 1 AS `1` from `test`.`catalog_category_flat_store_1` `main_table` left join `test`.`core_url_rewrite` `url_rewrite` on(((`test`.`url_rewrite`.`category_id` = `test`.`main_table`.`entity_id`) and (`test`.`url_rewrite`.`is_system` = 1) and (`test`.`url_rewrite`.`store_id` = 1) and (`test`.`url_rewrite`.`id_path` like 'category/%'))) where ((`test`.`main_table`.`include_in_menu` = '1') and (`test`.`main_table`.`is_active` = '1') and (`test`.`main_table`.`path` like '1/2/%')) order by `test`.`main_table`.`position` 5.6 revno 6060: 102 rows in set (0.13 sec) | 1 | SIMPLE | main_table | ALL | IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH | NULL | NULL | NULL | 143 | 98.60 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | url_rewrite | range | IDX_CORE_URL_REWRITE_TARGET_PATH_STORE_ID,IDX_CORE_URL_REWRITE_ID_PATH,IDX_CORE_URL_REWRITE_STORE_ID,FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID | IDX_CORE_URL_REWRITE_ID_PATH | 768 | NULL | 1138 | 100.00 | Using where; Using join buffer (Block Nested Loop) | /* select#1 */ select 1 AS `1` from `test`.`catalog_category_flat_store_1` `main_table` left join `test`.`core_url_rewrite` `url_rewrite` on(((`test`.`url_rewrite`.`store_id` = 1) and (`test`.`url_rewrite`.`is_system` = 1) and (`test`.`url_rewrite`.`category_id` = `test`.`main_table`.`entity_id`) and (`test`.`url_rewrite`.`id_path` like 'category/%'))) where ((`test`.`main_table`.`include_in_menu` = '1') and (`test`.`main_table`.`is_active` = '1') and (`test`.`main_table`.`path` like '1/2/%')) order by `test`.`main_table`.`position`

13 older comments

Hide

Permalink

MG added a comment - 2015-03-26 01:46

With this patch on 5.5.42, I have gotten a few different plans depending on what table statistics happen to be, each not using "Range checked for each record (index map: 0x1E)" and several orders of magnitude faster than unpatched 5.5.42 which uses "Range checked for each record (index map: 0x1E)" for my sample data and query.

Show

MG added a comment - 2015-03-26 01:46 With this patch on 5.5.42, I have gotten a few different plans depending on what table statistics happen to be, each not using "Range checked for each record (index map: 0x1E)" and several orders of magnitude faster than unpatched 5.5.42 which uses "Range checked for each record (index map: 0x1E)" for my sample data and query.

Hide

Permalink

Daniel Black added a comment - 2015-04-01 11:33

Sergei Petrunia , really keen to see your patch committed. While I'm still not sure why what "sel->quick->read_time" and "tab->table->file->scan_time()" are exactly (happy to remain ignorant for a while - I trust you), it does look better than rather arbitrary looking 100 row limit. While its always tough doing these changes for fear of regressions this is already a regression from 5.5.33. Magento has a not insignificant user base who would notice the difference between a 0.13 sec query and a 10 minute query once they reached a not too high limit on categories.

Show

Daniel Black added a comment - 2015-04-01 11:33 Sergei Petrunia , really keen to see your patch committed. While I'm still not sure why what "sel->quick->read_time" and "tab->table->file->scan_time()" are exactly (happy to remain ignorant for a while - I trust you), it does look better than rather arbitrary looking 100 row limit. While its always tough doing these changes for fear of regressions this is already a regression from 5.5.33. Magento has a not insignificant user base who would notice the difference between a 0.13 sec query and a 10 minute query once they reached a not too high limit on categories.

Hide

Permalink

Daniel Black added a comment - 2015-05-12 07:31

can we get this patch into the next 5.5/10.0 release?

Show

Daniel Black added a comment - 2015-05-12 07:31 can we get this patch into the next 5.5/10.0 release?

Hide

Permalink

Sergei Petrunia added a comment - 2015-06-10 18:41

Fix was pushed into 5.5 tree.

Show

Sergei Petrunia added a comment - 2015-06-10 18:41 Fix was pushed into 5.5 tree.

Hide

Permalink

Sergei Petrunia added a comment - 2015-06-10 18:43

Daniel Black, thanks for all the input on the issue, also for your patience when waiting for it to be pushed!

Show

Sergei Petrunia added a comment - 2015-06-10 18:43 Daniel Black , thanks for all the input on the issue, also for your patience when waiting for it to be pushed!

Range checked for each record used with key

Details

Description

Gliffy Diagrams

Attachments

Attachments

Issue Links

Activity

People

Dates

Agile