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

DELETE/UPDATE and ORDER BY ... LIMIT - the choice is not cost-based

Details

    Description

      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 t20 (a int, b int, c int, filler char(100));
      		 
      insert into t20 select a,a,a,a from one_k;
      		 
      alter table t20 add key(a), add key(b);


      mysql> explain delete from t20  where a  between 1 and 300 order by b limit 1;
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
      		 
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                       |
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
      		 
      |    1 | SIMPLE      | t20   | range | a             | a    | 5       | NULL |  300 | Using where; Using filesort |
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+

      Here, it will use range + sorting regardless of whether it is possible to use some other index that would match the LIMIT.

      For comparison:

      mysql> explain delete from t20  where a+0  between 1 and 300 order by b limit 1;
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      		 
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      		 
      |    1 | SIMPLE      | t20   | index | NULL          | b    | 5       | NULL |    1 | Using where |
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17761 Odd optimizer choice with ORDER BY LIMIT and condition selectivity

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Stalled

          Activity

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia added a comment -

            This was intended this way by the code in get_index_for_order. 

              if (select && select->quick)
            		 
              {
            		 
                ...
            		 
              }
            		 
              else if (limit != HA_POS_ERROR)
            		 
              { // check if some index scan & LIMIT is more efficient than filesort
            		 
                ...

            psergei Sergei Petrunia added a comment - This was intended this way by the code in get_index_for_order . if (select && select->quick) { ... } else if (limit != HA_POS_ERROR) { // check if some index scan & LIMIT is more efficient than filesort ...
            psergei Sergei Petrunia added a comment -

            The worse part is that quick select is constructed regardless of whether it is cheap (= and whether its condition is selective): SQL_SELECT::test_quick_select() is invoked with the limit parameter having the value from the LIMIT in the query. The effect of this is:

              if (limit < records)
            		 
                read_time= (double) records + scan_time + 1; // Force to use index

            which means a quick select is constructed if at all possible. For example:

            mysql> explain delete from t20  where a  between 1 and 900 order by b limit 1;
            		 
            +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
            		 
            | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                       |
            		 
            +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
            		 
            |    1 | SIMPLE      | t20   | range | a             | a    | 5       | NULL |  900 | Using where; Using filesort |
            		 
            +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+

            A plan to use range access to read 900 rows (out of 1000 in the table).
            Without ORDER BY LIMIT, it is not chosen:

            mysql> explain delete from t20  where a  between 1 and 900 ;
            		 
            +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
            		 
            | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
            		 
            +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
            		 
            |    1 | SIMPLE      | t20   | ALL  | a             | NULL | NULL    | NULL | 1000 | Using where |
            		 
            +------+-------------+-------+------+---------------+------+---------+------+------+-------------+

            psergei Sergei Petrunia added a comment - The worse part is that quick select is constructed regardless of whether it is cheap (= and whether its condition is selective): SQL_SELECT::test_quick_select() is invoked with the limit parameter having the value from the LIMIT in the query. The effect of this is: if (limit < records) read_time= ( double ) records + scan_time + 1; // Force to use index which means a quick select is constructed if at all possible. For example: mysql> explain delete from t20 where a between 1 and 900 order by b limit 1; +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | t20 | range | a | a | 5 | NULL | 900 | Using where; Using filesort | +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ A plan to use range access to read 900 rows (out of 1000 in the table). Without ORDER BY LIMIT, it is not chosen: mysql> explain delete from t20 where a between 1 and 900 ; +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t20 | ALL | a | NULL | NULL | NULL | 1000 | Using where | +------+-------------+-------+------+---------------+------+---------+------+------+-------------+

            People

              Unassigned Unassigned
              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.