The Ultimate Guide to High Availability with MariaDB

Virtual column support in the optimizer

You are viewing an old version of this article. View the current version here.

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.

Example

Suppose you have a table

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 and see that the optimizer can make use of the index:

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

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

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.