2

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)
David
  • 67
  • 1
  • 6
  • `d.name like '111'` will find only `'111'`. LIKE without wildcard character(s) makes no sense and must be replaced with `=`. – Akina Sep 29 '21 at 07:53
  • @Akina just for demonstration, and a replacement of "like" doesn't result in the use of index on the right table. – David Sep 29 '21 at 08:07
  • d.id is primary key, whilst d.name is not. that might be the cause – Carlo Prato Sep 29 '21 at 08:28

1 Answers1

0

this is caused by the like expression in the WHERE clause in the 2nd query

here is why, explained in some threads: 1- Equals(=) vs. LIKE 2- SQL 'like' vs '=' performance

EDIT looks like the issue here is caused by the fact that in the first query you are searching against the Primary Key, while in the second you dont

More details in this question: Behavior of WHERE clause on a primary key field

Carlo Prato
  • 326
  • 4
  • 21
  • Actually, index is used on the left table in both queries, where "like" is used. And the result is the same if I change "like" into "=". – David Sep 29 '21 at 08:00