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

EXPLAIN shows wrong info for ORDER BY query

    XMLWordPrintable

Details

    Description

      EXPLAIN output doesn't match query execution for a query.

      create table ten(a int);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table one_k(a int);
      insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
       
      create table t2 (key1 int, col1 int, key(key1));  
      insert into t2 select a,a from ten;
      insert into t2 select 15,15 from one_k;
      alter table t2 add key2 int, add key(key2);

      explain 
      select * from t2 ignore index for order by (key1) where col1<0 order by key1 limit 10;
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t2    | index | NULL          | key1 | 5       | NULL |   10 | Using where |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

      This looks odd - why does the optimizer use index key1 when we have explicitly specified *ignore index for order by (key1)* ?

      Let's run the SELECT itself:

      MariaDB [j2]> flush status;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [j2]> select * from t2 ignore index for order by (key1)  where col1<0 order by key1 limit 10;
      Empty set (0.03 sec)
       
      MariaDB [j2]> show status like '%sort%';
      +---------------------------+-------+
      | Variable_name             | Value |
      +---------------------------+-------+
      | Sort_merge_passes         | 0     |
      | Sort_priority_queue_sorts | 1     |
      | Sort_range                | 0     |
      | Sort_rows                 | 0     |
      | Sort_scan                 | 1     |
      +---------------------------+-------+
      5 rows in set (0.01 sec)

      It shows that the query did use filesort.

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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