Virtual Column Support in the Optimizer

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

Consider this table with data in JSON format:

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

Before MariaDB 11.8, you had to use vcol1 in the WHERE clause. Now, you can use the virtual column expression, too:

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 in MariaDB ().

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

• The optimization is implemented in a way similar to MySQL – the optimizer finds potentially useful occurrences of vcol_expr in the WHERE

1. Improved Optimizer plans for SELECT statements with ORDER BY or GROUP BY virtual columns when the virtual column expressions are covered by indexes that can be used.

2. Improved Optimizer plans for SELECT statements with ORDER BY or GROUP BY virtual columns expressions, by substitution of the virtual column expressions with virtual columns when the virtual columns are usable indexes themselves.

Accessing JSON fields

Cast the Value to the Desired Type

SQL is strongly-typed language while JSON is weakly-typed. This means one must specify the desired datatype 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 WHERE clause in SELECT query:):

Specify the Collation for Strings

When extracting string values, CAST is not necessary, as JSON_VALUE returns strings. However, you must take into account collations. Consider this column declared as JSON:

The collation of json_data is utf8mb4_bin. The collation of JSON_VALUE(json_data, ...) is utf8mb4_bin, too.

Most use cases require a more commonly-used collation. It is possible to achieve that using the COLLATE clause:

References

• : Add basic optimizer support for virtual columns

_{This page is licensed: CC BY-SA / Gnu FDL}