Indexing on JSON elements

from the Artful MySQL Tips List


On the following approach to indexing a 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


Return to the Artful MySQL Tips page