| Bug #101665 | mysql use incorrect index during executing "select" query | ||
|---|---|---|---|
| Submitted: | 18 Nov 2020 17:58 | Modified: | 19 Nov 2020 13:06 |
| Reporter: | Daniil Kamenskiy | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.22 | OS: | Linux |
| Assigned to: | CPU Architecture: | x86 | |
[19 Nov 2020 13:06]
MySQL Verification Team
Hi Mr. Kamenskiy, Thank you for your bug report. I have managed to repeat your test case and I have got the same results. I even used a totally different of values for the column `d`, which is better. This is just a small bug in the display of the EXPLAIN statement in cases like the one presented here. Regarding locking, this query is scanning the entire index, which is why all rows are locked, when the FOR UPDATE statement is used. Verified as reported.
Description: Hi, Hello, i faced strange explain. mysql> select version(); +-------------------------+ | version() | +-------------------------+ | 8.0.22-0ubuntu0.20.04.2 | +-------------------------+ 1 row in set (0.00 sec) mysql> create table tt(id int primary key auto_increment, d int, key d(d)); Query OK, 0 rows affected (0.02 sec) mysql> insert into tt(d) values(1),(2),(3),(4),(5),(6); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> explain select * from tt where id in (1,2,3)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tt partitions: NULL type: index possible_keys: PRIMARY key: d key_len: 5 ref: NULL rows: 6 filtered: 50.00 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec) Why does mysql use key "d"? It have to use primary, isn't it? If I use "for update" construction in this query (in transaction), then it's impossible to insert the record in the same table in the another thread, because another thread waits finish of first transaction. If we use hint use index(primary) - then result is correct. mysql> explain select * from tt use index(primary) where id in (1,2,3)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tt partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 3 filtered: 100.00 Extra: Using where How to repeat: create table tt(id int primary key auto_increment, d int, key d(d)); insert into tt(d) values(1),(2),(3),(4),(5),(6); begin; (optionally) explain select * from tt where id in (1,2,3) [for update, optionally]