[MDEV-24974] Query optimizer not use index with ORDER BY with CASE Created: 2021-02-24  Updated: 2021-03-28

Status: Open
Project: MariaDB Server
Component/s: Optimizer, Server, Storage Engine - InnoDB
Affects Version/s: 10.3.27
Fix Version/s: 10.3

Type: Bug Priority: Major
Reporter: DAIKOZ Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: innodb, performance
Environment:

Server version: 10.3.27-MariaDB-0+deb10u1-log Debian 10


 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|
Generated at Thu Feb 08 09:34:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.