Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.0(EOL), 11.5(EOL), 11.6(EOL)
-
None
-
Official docker image
Host : Ubuntu 24.04 - amd64
Description
Starting with mariadb 11.0, some index are ignored when runing a sub query on the same table as the main query.
Tester versions
- 10.11.9 -> Ok
- 11.0.6 -> Index selection issue
- 11.5.2 -> Index selection issue
- 11.6.1 (RC) -> Index selection issue
Reproduction
Data setup
DROP TABLE IF EXISTS `tmp`; |
CREATE TABLE `tmp` ( |
`id` INT NOT NULL AUTO_INCREMENT, |
`reference` varchar(64) DEFAULT NULL, |
`mainLine_fk` INT DEFAULT NULL, |
PRIMARY KEY (`id`), |
KEY `mainLineIndex` (`mainLine_fk`), |
CONSTRAINT `mainLineFk` FOREIGN KEY (`mainLine_fk`) REFERENCES `tmp` (`id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; |
|
|
DROP PROCEDURE IF EXISTS insertProcedure; |
CREATE PROCEDURE insertProcedure() |
BEGIN
|
DECLARE i int DEFAULT 1; |
TRUNCATE TABLE tmp; |
WHILE i <= 50000 DO
|
INSERT INTO tmp (id, reference, mainLine_fk) VALUES (i, LEFT(UUID(), 8), null); |
SET i = i + 1; |
END WHILE; |
END; |
|
|
CALL insertProcedure();
|
|
|
UPDATE tmp SET mainLine_fk = 4005 WHERE id > 60 AND id < 80; |
Query
SELECT `root`.`id` AS id, |
(SELECT GROUP_CONCAT(subLines.`reference`) |
FROM `tmp` subLines |
WHERE `root`.`id` = subLines.`mainLine_fk`) AS subLineRefs |
FROM `tmp` `root` |
WHERE `root`.`id` > 4000 AND `root`.`id` < 4500; |
In this query, the `mainLineIndex` key is not used with mariadb 11.5.2:
The query is slow (~1.5s)
+------+--------------------+----------+-------+---------------+---------+---------+------+-------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+----------+-------+---------------+---------+---------+------+-------+-------------+
|
| 1 | PRIMARY | root | range | PRIMARY | PRIMARY | 4 | NULL | 499 | Using where |
|
| 2 | DEPENDENT SUBQUERY | subLines | ALL | mainLineIndex | NULL | NULL | NULL | 50436 | Using where |
|
+------+--------------------+----------+-------+---------------+---------+---------+------+-------+-------------+
|
With mariadb = 10.11.9, the `mainLineIndex` is used:
The query is very fast (~20ms)
+------+--------------------+----------+-------+---------------+---------------+---------+-------------+-------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+----------+-------+---------------+---------------+---------+-------------+-------+--------------------------+
|
| 1 | PRIMARY | root | range | PRIMARY | PRIMARY | 4 | NULL | 499 | Using where; Using index |
|
| 2 | DEPENDENT SUBQUERY | subLines | ref | mainLineIndex | mainLineIndex | 5 | blg.root.id | 24871 | |
|
+------+--------------------+----------+-------+---------------+---------------+---------+-------------+-------+--------------------------+
|
Using `FORCE INDEX`, we can use the index with mariadb > 11, and get the fast performance (~20ms) :
SELECT `root`.`id` AS id, |
(SELECT GROUP_CONCAT(subLines.`reference`) |
FROM `tmp` subLines |
FORCE INDEX (mainLineIndex) |
WHERE `root`.`id` = subLines.`mainLine_fk`) AS subLineRefs |
FROM `tmp` `root` |
WHERE `root`.`id` > 4000 AND `root`.`id` < 4500; |
+------+--------------------+----------+-------+---------------+---------------+---------+-------------+-------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+----------+-------+---------------+---------------+---------+-------------+-------+-------------+
|
| 1 | PRIMARY | root | range | PRIMARY | PRIMARY | 4 | NULL | 499 | Using where |
|
| 2 | DEPENDENT SUBQUERY | subLines | ref | mainLineIndex | mainLineIndex | 5 | blg.root.id | 25218 | |
|
+------+--------------------+----------+-------+---------------+---------------+---------+-------------+-------+-------------+
|
`USE INDEX` has no effect.