I have 2 tables:
CREATE TABLE `directory` (
`id` bigint NOT NULL,
`datasets` json DEFAULT NULL
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_datasets` ((cast(`datasets` as unsigned array)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TABLE `dataset` (
`id` bigint NOT NULL,
`name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_name` (`name`,`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
The query below uses indexes on the two tables as expected:
explain
SELECT * FROM dataset d inner join `directory` dir
on JSON_CONTAINS(dir.datasets, cast(d.id as json))
where d.id = 111;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE d const PRIMARY PRIMARY 8 const 1 100.00
1 SIMPLE dir range idx_datasets idx_datasets 9 2 100.00 Using where
However, this query uses index only on the left table
explain
SELECT * FROM dataset d inner join `directory` dir
on JSON_CONTAINS(dir.datasets, cast(d.id as json))
where d.name like '111';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE d range idx_name idx_name 259 1 100.00 Using index condition
1 SIMPLE dir ALL 1000 100.00 Using where; Using join buffer (hash join)
Could someone explain the difference between the two queries?
I change the condition "like" to "=", the result is the same:
explain
SELECT * FROM dataset d inner join catalog dir
on JSON_CONTAINS(dir.datasets, cast(d.id as json))
where d.name = '111';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE d ref idx_name idx_name 259 const 1 100.00
1 SIMPLE dir ALL 1000 100.00 Using where; Using join buffer (hash join)