| Bug #105068 | Type ref instead of range after reconsidering_access_paths_for_index_ordering. | ||
|---|---|---|---|
| Submitted: | 28 Sep 2021 22:06 | Modified: | 30 Sep 2021 15:00 |
| Reporter: | Jean-François Gagné | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.7 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[29 Sep 2021 11:42]
Øystein Grøvlen
I think this issue was fixed in 8.0.14 by the fix for Bug#28086754. The fix for this bug in 5.7.25 is slightly different and does not include the call to can_switch_from_ref_to_range() in test_if_skip_sort_order(). In other words, I think this bug will only appear in 5.7.
[29 Sep 2021 12:37]
MySQL Verification Team
Hi Mr. Gagne, Thank you for your bug report. In order to proceed with processing of the bug report we require a full test case. A test case that consists of a set of SQL statements that lead to the appearance of the bug. While we are waiting for the response from you, we shall analyse whether this behaviour will be fixed in 5.7 or not.
[29 Sep 2021 12:39]
MySQL Verification Team
Hi, To make ourselves more clear, your uploaded file does not contain any rows.
[29 Sep 2021 13:07]
Jean-François Gagné
Hi MySQL Verification Team, > In order to proceed with processing of the bug report we require a full test case. A test case that consists of a set of SQL statements that lead to the appearance of the bug. I think I already provide this in the private file I uploaded. I was hopping that the explain outputs and the optimizer trace would be enough to do a code analysis. Also, maybe what was provided by Øystein could be considered for finding this bug by code inspection. > To make ourselves more clear, your uploaded file does not contain any rows. I am able to see the content of the private file that I uploaded (2nd private comment, 45.07 KiB). This file contains a little more data than what I can publicly disclose (table structure and SQL statements), but the dataset is not shared (production / customer data - 16 GB ibd file) and the statistics on the table are not shared either (I do not know hot to extract this from my production db and inject this back in my test environment, which could lead to a reproduction with 5.7.25 non-prod, 5.7.35 and maybe with 8.0.26). Hopefully, we can work together to narrow this down with the data already shared publicly and privately, and I am at your disposition to provide more information (public or private), just let me know what would help. Best regards, Jean-François Gagné
[29 Sep 2021 13:13]
MySQL Verification Team
Hi, We have looked at all data that you have sent us and there are no rows that will help us repeat the behaviour. We do accept code analysis, with pinpoint of the error in our code, but we have not received that either from you. Meanwhile, we shall investigate whether this is fixed or not in 8.0.
[29 Sep 2021 20:07]
Øystein Grøvlen
The following test case, seems to reproduce the problem:
create table t1(pk int auto_increment primary key, a int, b int, c int, key (a, b), key (c));
insert into t1(a, b, c)
values (1,1,1), (1,2,2), (1,3,3), (1,4,4), (1,5,5), (2,1,6), (2,2,7), (2,3,8), (2,4,9), (2,5,10);
insert into t1(a,b,c) select a,b,c from t1;
insert into t1(a,b,c) select a,b,c from t1;
insert into t1(a,b,c) select a,b,c from t1;
analyze table t1;
explain select * from t1 where a=1 and b>1 and c<2 order by b desc LIMIT 1;
EXPLAIN output:
mysql [localhost:5732] {msandbox} (test) > explain select * from t1 where a=1 and b>1 and c<2 order by b desc LIMIT 1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | a,c | a | 5 | const | 40 | 3.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
As you can see, ref is used here even if it is possible to use range on more keyparts.
[29 Sep 2021 20:31]
Jean-François Gagné
I am a little embarrassed, and it looks like I own excuses... I was not able to reproduce after importing the ibd in 5.7-latest and 8.0-latest and in 5.7.25 for a good reason, and not because of statistics... I was able to reproduce in *Percona Server* 5.7-latest and have to confess that this behavior was not happening in MySQL 5.7.25 but in Percona Server 5.7.25. :-S Thanks to Øystein for pointing-out that my full optimizer trace was including things that were not in MySQL 5.7. From Øystein reproduction, it still looks like there is something to be fixed in MySQL 5.7, I am not sure what is the best way forward here: keeping this bug open for Øystein findings or opening a new bug. (I will post a link to PS bug after opening it.) I am very sorry for having wasted your time.
[30 Sep 2021 12:23]
MySQL Verification Team
Hi All, This bug is fixed in our version 8.0 and it will not be analysed nor fixed in any of the previous versions, even if it would surface ...... Thank you both for your contribution.
[30 Sep 2021 12:37]
MySQL Verification Team
Hi, After additional analysis, it was decided that this bug will be verified as affecting only version 5.7. Verified as 5.7-only bug .....
[30 Sep 2021 14:51]
Jean-François Gagné
This might be a duplicate of Bug#92850.
[4 Oct 2021 12:07]
MySQL Verification Team
Merci, Jean-Francois !!!!
Description: Hi, sorry, this is a little convoluted, and I am not able to reproduce on latest version, either because the bug is not there (not sure about this), or because it is dependent on statistics (this is my hypothesis). I have a query that I would expect to be fast (using an index and short-circuiting because of limit) but it ends-up being slow and scanning more rows than it should. If I hint the good index, the query becomes fast. I traced this down to type ref (instead of range when hinting the index) in the explain output. Using optimizer tracing, I can see that in the beginning, the right index is not chosen (probably because of statistics), but at step reconsidering_access_paths_for_index_ordering, the right index get picked-up but with access_type ref. So I guess something when wrong at this step of the optimizer. I cannot publicly provide full CREATE TABLE statement, queries and optimizer trace because this is production / customer data. Sorry about this and for the complexity it introduces in How to repeat. I have the table imported in MySQL 5.7.35 and 8.0.26, but I am not able to reproduce there (I get the good range plan). I also imported in 5.7.25 and also not able to reproduce there, which makes me think statistics are important. Let me know if you want me to test more things. Many thanks for looking into this, Jean-François Gagné How to repeat: ####################### # Partial CREATE TABLE: CREATE TABLE `c` ( `pid` bigint(20) NOT NULL DEFAULT '0', `id` bigint(20) NOT NULL, `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `cid` smallint(5) unsigned NOT NULL, `cgid` bigint(20) NOT NULL DEFAULT '0', `state` varchar(25) NOT NULL DEFAULT '', `pd` bigint(20) DEFAULT NULL, `deleted_at` bigint(20) NOT NULL DEFAULT '0', [...] PRIMARY KEY (`pid`,`id`), [...] KEY `i1` (`pid`,`cid`,`cgid`,`state`,`pd`,`cbid`), [...] ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 ####################################################################### # Partial bad EXPLAIN (:pid, etc... are actual value that I must hide): mysql> explain select id, name from c where pid = :pid and cid = :cid and cgid = :cgid and state = :state and pd < :pd and deleted_at = 0 order by pd desc limit 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: c partitions: NULL type: ref possible_keys: PRIMARY,[...] key: i1 key_len: 95 ref: const,const,const,const rows: 56636 filtered: 0.47 Extra: Using where 1 row in set, 1 warning (0.00 sec) ################ # Bad execution: mysql> flush local status; pager cat > /dev/null; select id, name from c where pid = :pid and cid = :cid and cgid = :cgid and state = :state and pd < :pd and deleted_at = 0 order by pd desc limit 1; pager; show status like "Handler_read%"; Query OK, 0 rows affected (0.00 sec) PAGER set to 'cat > /dev/null' 1 row in set (0.30 sec) Default pager wasn't set, using stdout. +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 18651 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 7 rows in set (0.00 sec) ################################## # Good explain and good execution: mysql> explain select id, name from c use index (i1) where pid = :pid and cid = :cid and cgid = :cgid and state = :state and pd < :pd and deleted_at = 0 order by pd desc limit 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: c partitions: NULL type: range possible_keys: i1 key: i1 key_len: 104 ref: NULL rows: 12052 filtered: 10.00 Extra: Using index condition; Using where 1 row in set, 1 warning (0.00 sec) mysql> flush local status; pager cat > /dev/null; select id, name from c use index (i1) where pid = :pid and cid = :cid and cgid = :cgid and state = :state and pd < :pd and deleted_at = 0 order by pd desc limit 1; pager; show status like "Handler_read%"; Query OK, 0 rows affected (0.00 sec) PAGER set to 'cat > /dev/null' 1 row in set (0.00 sec) Default pager wasn't set, using stdout. +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 7 rows in set (0.00 sec) #################################################################################### # Because Index Condition Pushdown is used in the hinted plan (with the index hint), it "muddles the water" (to take back Øystein words from the Slack extract in Bug#104659). So to make handler stats clearer, this is the hinted plan with ICP disabled: mysql> SET optimizer_switch = 'index_condition_pushdown=off'; mysql> explain select id, name from c use index (i1) where pid = :pid and cid = :cid and cgid = :cgid and state = :state and pd < :pd and deleted_at = 0 order by pd desc limit 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: c partitions: NULL type: range possible_keys: i1 key: i1 key_len: 104 ref: NULL rows: 12052 filtered: 10.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) mysql> flush local status; pager cat > /dev/null; select id, name from c use index (i1) where pid = :pid and cid = :cid and cgid = :cgid and state = :state and pd < :pd and deleted_at = 0 order by pd desc limit 1; pager; show status like "Handler_read%"; Query OK, 0 rows affected (0.00 sec) PAGER set to 'cat > /dev/null' 1 row in set (0.00 sec) Default pager wasn't set, using stdout. +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 7 rows in set (0.00 sec) ################################################################################ # Looking at the optimizer trace, we can see that the good index is not taken... SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G *************************** 1. row *************************** QUERY: select id, name from c where pid = :pid and cid = :cid and cgid = :cgid and state = :state and pd < :pd and deleted_at = 0 order by pd desc limit 1 TRACE: { "steps": [ [...] "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ ":pid <= pid <= :pid" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 58608, "cost": 12269, "chosen": true }, [...] { "index": "i1", "ranges": [ ":pid <= pid <= :pid AND :cid <= cid <= :cid AND :cgid <= cgid <= :cgid AND :state <= state <= :state AND NULL < pd < :pd" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 12052, "cost": 14463, "chosen": false, "cause": "cost" }, [...] ################################# # But a little later in the trace, we see the good index is chosen back, but with access_type ref instead of range (the hinted query which performs well has range): { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "steps": [ ], "index_order_summary": { "table": "`contents`", "index_provides_order": true, "order_direction": "desc", "index": "i1", "plan_changed": true, "access_type": "ref" } } ######################################################################################################################################### # I suspect the bad plan is scanning all the index without using the "pd < :pd" part of the query which is there to be used in the index, and this is somehow confirmed by the results below (and note that all rows are deleted_at = 0). mysql> select count(*) from c where pid = :pid and cid = :cid and cgid = :cgid and state = :state; +----------+ | count(*) | +----------+ | 24033 | +----------+ 1 row in set (0.01 sec) mysql> select count(*) from c where pid = :pid and cid = :cid and cgid = :cgid and state = :state and pd < :pd; +----------+ | count(*) | +----------+ | 5382 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from c where pid = :pid and cid = :cid and cgid = :cgid and state = :state and deleted_at <> 0; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.02 sec) # Thanks for following up to here and sorry for the anonymized data.