JSON element ...
drop table if exists t; create table t ( id binary(16) primary key, data json not null ) engine=innodb; create index idx on t (cast(data->>"$.name" as char(10)));... the MySQL query engine fails to index-optimise the
LIKE operator ...
explain select *
from t force index(idx)
where cast(data->>'$.name' as char(10)) LIKE 'app-1'\G
id: 1
select_type: SIMPLE
table: applications
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
But if we create a generated column for the index, the LIKE clause can be index-optimised ...
drop table if exists t;
create table t (
id serial,
doc json,
virtual_name char(20) generated always as (doc->"$.name"),
index i(virtual_name)
);
explain select *
from t
where virtual_name like 'app-'\G
id: 1
select_type: SIMPLE
table: app
partitions: NULL
type: range
possible_keys: i
key: i
key_len: 81
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
Last updated 26 Feb 2021
