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 you have a table with a JSON field:

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

You add a virtual column and an index on it:

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

Then, you can use the virtual column expression in the WHERE clause.

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

The optimizer will be able to use an index:

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

General considerations

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

• In other databases, one can just add an index on expression: create index on t1((col1+col2)). In MariaDB, one has to explicitly declare a virtual column. This is a limitation.

Considerations for accessing JSON fields

SQL is strongly-typed language and JSON is weakly-typed. This means one must specify the desired datatypes when accessing JSON data from SQL. The above example uses:

cast(json_value(json_data, '$.column1') as integer). 

References

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