Description:
Hello,
i fiddled around with JSON documents and tried out the new multi valued indexes and realized that when column references are used instead of a static values the optimizer ignores the index.
Is this intended behaviour?
sincerly,
Steven Günther
How to repeat:
-- jsontest.`data` definition
CREATE TABLE jsontest (
`id` int NOT NULL AUTO_INCREMENT,
`docId` int unsigned NOT NULL,
`data` json NOT NULL,
PRIMARY KEY (`id`),
KEY `subIds` ((cast(`data`->'$.subDocs[*].subId' as unsigned array)))
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `jsontest` (docId,`data`) VALUES
(1111111,'{"docId": 1111111, "subDocs": [{"subId": 1111111}, {"subId": 1111112}]}')
,(2222222,'{"docId": 2222222, "subDocs": [{"subId": 2222222}]}')
,(3333334,'{"docId": 3333334, "subDocs": [{"subId": 3333334}]}')
,(3333335,'{"docId": 3333335, "subDocs": [{"subId": 3333334}, {"subId": 3333335}]}');
----- WORKING AS EXPECTED -----
mysql> EXPLAIN SELECT d.docId FROM jsontest d WHERE 3333334 MEMBER OF (d.data->'$.subDocs[*].subId') \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: d
partitions: NULL
type: ref
possible_keys: subIds
key: subIds
key_len: 9
ref: const
rows: 1
filtered: 100.00
Extra: Using where
mysql> SHOW WARNINGS \G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `jsontest`.`d`.`docId` AS `docId` from `jsontest`.`jsontest` `d` where json'3333334' member of (cast(json_extract(`jsontest`.`d`.`data`,_utf8mb4'$.subDocs[*].subId') as unsigned array))
1 row in set (0,00 sec)
----- WORKING AS EXPECTED -----
----- INDEX GOT IGNORED UNEXPECTED -----
mysql> EXPLAIN SELECT d.docId FROM jsontest d WHERE d.docId MEMBER OF (d.data->'$.subDocs[*].subId') \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: d
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0,00 sec)
mysql> SHOW WARNINGS \G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `jsontest`.`d`.`docId` AS `docId` from `jsontest`.`jsontest` `d` where `jsontest`.`d`.`docId` member of (json_extract(`jsontest`.`d`.`data`,'$.subDocs[*].subId'))
1 row in set (0,00 sec)
----- INDEX GOT IGNORED UNEXPECTED -----
Description: Hello, i fiddled around with JSON documents and tried out the new multi valued indexes and realized that when column references are used instead of a static values the optimizer ignores the index. Is this intended behaviour? sincerly, Steven Günther How to repeat: -- jsontest.`data` definition CREATE TABLE jsontest ( `id` int NOT NULL AUTO_INCREMENT, `docId` int unsigned NOT NULL, `data` json NOT NULL, PRIMARY KEY (`id`), KEY `subIds` ((cast(`data`->'$.subDocs[*].subId' as unsigned array))) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO `jsontest` (docId,`data`) VALUES (1111111,'{"docId": 1111111, "subDocs": [{"subId": 1111111}, {"subId": 1111112}]}') ,(2222222,'{"docId": 2222222, "subDocs": [{"subId": 2222222}]}') ,(3333334,'{"docId": 3333334, "subDocs": [{"subId": 3333334}]}') ,(3333335,'{"docId": 3333335, "subDocs": [{"subId": 3333334}, {"subId": 3333335}]}'); ----- WORKING AS EXPECTED ----- mysql> EXPLAIN SELECT d.docId FROM jsontest d WHERE 3333334 MEMBER OF (d.data->'$.subDocs[*].subId') \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: d partitions: NULL type: ref possible_keys: subIds key: subIds key_len: 9 ref: const rows: 1 filtered: 100.00 Extra: Using where mysql> SHOW WARNINGS \G; *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select `jsontest`.`d`.`docId` AS `docId` from `jsontest`.`jsontest` `d` where json'3333334' member of (cast(json_extract(`jsontest`.`d`.`data`,_utf8mb4'$.subDocs[*].subId') as unsigned array)) 1 row in set (0,00 sec) ----- WORKING AS EXPECTED ----- ----- INDEX GOT IGNORED UNEXPECTED ----- mysql> EXPLAIN SELECT d.docId FROM jsontest d WHERE d.docId MEMBER OF (d.data->'$.subDocs[*].subId') \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: d partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0,00 sec) mysql> SHOW WARNINGS \G; *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select `jsontest`.`d`.`docId` AS `docId` from `jsontest`.`jsontest` `d` where `jsontest`.`d`.`docId` member of (json_extract(`jsontest`.`d`.`data`,'$.subDocs[*].subId')) 1 row in set (0,00 sec) ----- INDEX GOT IGNORED UNEXPECTED -----