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:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.22 OS:Linux
Assigned to: CPU Architecture:x86

[18 Nov 2020 17:58] Daniil Kamenskiy
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]
[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.