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

Query optimizer not use index with ORDER BY with CASE

    XMLWordPrintable

    Details

      Description

      Performance issue when use CASE with ORDER BY: index is not used.

      When do:

      SELECT * FROM mytable ORDER BY id;
      

      => the index on primary key is used

      When do on same table:

      SET @sortBy = 'id';
      SELECT * FROM mytable ORDER BY CASE WHEN @sortBy = 'id' THEN id END;
      

      => all rows are parsed (performance issue on large table). No index of primary key is used even if variable @sortBy is defined before the query.

      To simplify, I create this example:

       
      CREATE TABLE IF NOT EXISTS `docs` (
        `id` int(6) unsigned NOT NULL,
        `rev` int(3) unsigned NOT NULL,
        `content` varchar(200) NOT NULL,
        PRIMARY KEY (`id`,`rev`)
      ) DEFAULT CHARSET=utf8;
      INSERT INTO `docs` (`id`, `rev`, `content`) VALUES
        ('1', '1', 'The earth is flat'),
        ('2', '1', 'One hundred angels can dance on the head of a pin'),
        ('1', '2', 'The earth is flat and rests on a bull\'s horn'),
        ('1', '3', 'The earth is like a ball.');
      
      

      EXPLAIN SELECT a.id, a.rev, a.content
      FROM `docs` a
      ORDER BY id;
      

      id|select_type|table|type |possible_keys|key |key_len|ref|rows|Extra|
      -|---------|---|---|-----------|-----|-----|-|--|----|
      1|SIMPLE |a |index| |PRIMARY|8 | | 4| |

      SET @sortBy = 'id';
       
      EXPLAIN SELECT a.id, a.rev, a.content
      FROM `docs` a
      ORDER BY CASE WHEN @sortBy = 'id' THEN id END;
      

      id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra |
      -|---------|---|--|-----------|-|-----|-|--|-------------|
      1|SIMPLE |a |ALL | | | | | 4|Using filesort|

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            daikoz DAIKOZ
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration