Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-4949

Bad select+order by+limit performance in 5.7

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 5.7.25-28
    • Component/s: None
    • Labels:

      Description

      Description:
      Similar to PS-4617 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  where grp = 4 and seq <= 1851933 order by grp desc, seq desc limit 1;
      +---------+-----+----------------------------------+
      | seq     | grp | txt                              |
      +---------+-----+----------------------------------+
      | 1851933 |   4 | 64cbf702af49857b19c8d5890eb476a2 |
      +---------+-----+----------------------------------+
      1 row in set (19.04 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 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)
      

       

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                sergei.glushchenko Sergei Glushchenko
                Reporter:
                sveta.smirnova Sveta Smirnova
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 3 days, 5 hours, 7 minutes
                  3d 5h 7m