MySQL Forums
Forum List  »  General

LIKE operator on indexed JSON attribute does not use index
Posted by: Ken Tran
Date: February 25, 2021 11:53PM

Suppose we have this table with an index on a virtual generated column as JSON attribute index pattern

```
CREATE TABLE applications (
id BINARY(16) PRIMARY KEY,
data JSON NOT NULL,
) ENGINE=InnoDB;
CREATE INDEX idx ON applications ((CAST(data->>"$.name" AS CHAR(10))));
```

For queries with `=` operator to compare the text field of data, the index is hit and works as expected

```
mysql> EXPLAIN SELECT * FROM applications FORCE INDEX (idx) WHERE CAST(data->>'$.name' AS CHAR(10)) = 'app-1';
+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | applications | NULL | ref | idx | idx | 13 | const | 1 | 100.00 | NULL |
+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
```

But for queries with `LIKE` operator, even in case search without wildcard, the indexes is not hit

```
mysql> EXPLAIN SELECT * FROM applications FORCE INDEX (idx) WHERE CAST(data->>'$.name' AS CHAR(10)) LIKE 'app-1';
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | applications | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM applications FORCE INDEX (idx) WHERE CAST(data->>'$.name' AS CHAR(10)) LIKE 'app-%';
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | applications | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
```

Options: ReplyQuote


Subject
Written By
Posted
LIKE operator on indexed JSON attribute does not use index
February 25, 2021 11:53PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.