Virtual column support in the optimizer

Starting from MariaDB 11.8, the optimizer can recognize use of indexed virtual column expressions in the WHERE clause and use them to construct range and ref(const) accesses.

Motivating example

Suppose one has a table with data in JSON:

create table t1 (json_data JSON);
insert into t1 values('{"column1": 1234}'); 
insert into t1 ...

In order to do efficient queries over data in JSON, one can add a virtual column and an index:

alter table t1
  add column vcol1 int as (cast(json_value(json_data, '$.column1') as integer)),
  add index(vcol1);

Before MariaDB 11.8, one had to use vcol1 in the WHERE clause. Now, one can use the virtual column expression, too. A query like this:

explain select * 
from t1 
where 
  cast(json_value(json_data, '$.column1') as integer)=100;

will be resolved through the index on vcol1:

+------+-------------+-------+------+---------------+-------+---------+-------+------+-------+
| id   | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra |
+------+-------------+-------+------+---------------+-------+---------+-------+------+-------+
|    1 | SIMPLE      | t1    | ref  | vcol1         | vcol1 | 5       | const | 1    |       |
+------+-------------+-------+------+---------------+-------+---------+-------+------+-------+

General considerations

• In MariaDB, one has to create a virtual column and then create an index over it. Other databases allow to create an index directly over expression: create index on t1((col1+col2)). This is not yet supported.

• The WHERE clause must use the exact same expression as in the virtual column definition.

• In the optimizer trace, the rewrites are shown like so: <<code:js>> "virtual_column_substitution": { "condition": "WHERE", "resulting_condition": "t1.vcol1 = 100" } <<code>>

Considerations for accessing JSON fields

SQL is strongly-typed language while JSON is weakly-typed. This means one must specify the desired datatypes when accessing JSON data from SQL. In the above example, we declared vcol1 as INT and then used (CAST ... AS INTEGER) (both in the ALTER TABLE and in the query):

alter table t1
  add column vcol1 int as (cast(json_value(json_data, '$.column1') as integer)) ...

When handling string values, CAST is not necessary, as JSON_VALUE returns strings. However, one must take into account collation. The collation of a column using JSON datatype is utf8_bin. If you need a different collation, use

alter table t1
  add column vcol1 int as (json_value(json_data, '$.column1') collate utf8_)),

References

• MDEV-35616: Add basic optimizer support for virtual columns