Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9504

ANALYZE TABLE shows wrong 'rows' value for ORDER BY query

    XMLWordPrintable

Details

    Description

      create table t1 (
        a int, 
        filler1 char(128),
        filler2 char(128),
        key(a)
      );
       
      insert into t1 
      select A.a+10*B.a+100*C.a, repeat('abc-',32), repeat('abc-',32)
      from ten A, ten B, ten C;

      EXPLAIN shows the correct estimate, we will read 10 rows:

      explain select a from t1 order by a limit 10;
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | index | NULL          | a    | 5       | NULL |   10 | Using index |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

      ANALYZE's rows is different from the EXPLAIN:

      analyze select a from t1 order by a limit 10;
      MariaDB [j1]> analyze select a from t1 order by a limit 10;
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+-------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+-------------+
      |    1 | SIMPLE      | t1    | index | NULL          | a    | 5       | NULL |  978 |  10.00 |   100.00 |     100.00 | Using index |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+-------------+

      The value is wrong, it ignores the LIMIT clause.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.