Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.27
-
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|