Description:
Similar to bug #91723 but repeatable after ANALYZE TABLE run.
How to repeat:
create table test(
seq int not null primary key auto_increment,
grp int not null,
txt varchar(64),
key ix_test(grp),
key ix_test2(grp,seq)
) engine=innodb;
insert into test (grp, txt) select 1, md5(rand()) from dual;
insert into test (grp, txt) select 1, md5(rand()) from test;
insert into test (grp, txt) select 1, md5(rand()) from test;
<repeat few times to insert more data>
mysql> analyze table test;
+-----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| test.test | analyze | status | OK |
+-----------+---------+----------+----------+
1 row in set (0.13 sec)
mysql> select * from test where grp = 4 and seq <= 1851933 order by grp desc, seq desc limit 1;
+---------+-----+----------------------------------+
| seq | grp | txt |
+---------+-----+----------------------------------+
| 1851933 | 4 | 64cbf702af49857b19c8d5890eb476a2 |
+---------+-----+----------------------------------+
1 row in set (17.82 sec)
mysql> select * from test use index(ix_test) where grp = 4 and seq <= 1851933 order by grp desc, seq desc limit 1;
+---------+-----+----------------------------------+
| seq | grp | txt |
+---------+-----+----------------------------------+
| 1851933 | 4 | 64cbf702af49857b19c8d5890eb476a2 |
+---------+-----+----------------------------------+
1 row in set (17.37 sec)
mysql> select * from test force index(ix_test) where grp = 4 and seq <= 1851933 order by grp desc, seq desc limit 1;
+---------+-----+----------------------------------+
| seq | grp | txt |
+---------+-----+----------------------------------+
| 1851933 | 4 | 64cbf702af49857b19c8d5890eb476a2 |
+---------+-----+----------------------------------+
1 row in set (0.01 sec)
Notice query execution time difference.
EXPLAIN is also different:
mysql> explain select * from test where grp = 4 and seq <= 1851933 order by grp desc, seq desc limit 1;
+----+-------------+-------+------------+------+--------------------------+---------+---------+-------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------------+---------+---------+-------+---------+----------+-------------+
| 1 | SIMPLE | test | NULL | ref | PRIMARY,ix_test,ix_test2 | ix_test | 4 | const | 1044500 | 50.00 | Using where |
+----+-------------+-------+------------+------+--------------------------+---------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.05 sec)
mysql> explain select * from test use index(ix_test) where grp = 4 and seq <= 1851933
+----+-------------+-------+------------+------+---------------+---------+---------+-------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+---------+----------+-------------+
| 1 | SIMPLE | test | NULL | ref | ix_test | ix_test | 4 | const | 1044500 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test force index(ix_test) where grp = 4 and seq <= 1851933
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-----------------------+
| 1 | SIMPLE | test | NULL | range | ix_test | ix_test | 8 | NULL | 1044500 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
Handler statistics:
mysql> flush status; Query OK, 0 rows affected (0.00 sec)
mysql> select * from test use index(ix_test) where grp = 4 and seq <= 1851933 order by grp desc, seq desc limit 1;
+---------+-----+----------------------------------+
| seq | grp | txt |
+---------+-----+----------------------------------+
| 1851933 | 4 | 64cbf702af49857b19c8d5890eb476a2 |
+---------+-----+----------------------------------+
1 row in set (17.71 sec)
mysql> show status like 'Handler_read%'; +-----------------------+--------+
| Variable_name | Value |
+-----------------------+--------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 572863 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+--------+
7 rows in set (0.00 sec)
mysql> flush status; Query OK, 0 rows affected (0.00 sec)
mysql> select * from test force index(ix_test) where grp = 4 and seq <= 1851933 order by grp desc, seq desc limit 1;
+---------+-----+----------------------------------+
| seq | grp | txt |
+---------+-----+----------------------------------+
| 1851933 | 4 | 64cbf702af49857b19c8d5890eb476a2 |
+---------+-----+----------------------------------+
1 row in set (0.01 sec)
mysql> show status like 'Handler_read%'; +-----------------------+-------+
| 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)
