Hi,

I am Richa Sehgal currently pursuing Master’s at University of Illinois Urbana Champaign, USA. I did my undergraduate from Indian Institute of Technology Delhi (IIT-Delhi). I would like to take this up as my GSoC project.

Materialization gives us two things:
1. A name to the column which we can use in queries
2. A formal "regular" column which is stored and indexed in the regular fashion - Disadvantage: Extra memory requirements for the materialized column.

My initial thoughts on this project are the following:
We do need the name of the column which can be used to query. So maybe we can expose a command such as:

create virtual_index <name> on <column_name> <expression>

What this would do would run a regular query which evaluates expressions (like in WHERE clause) and the feed the result into the indexer. This index can then be stored in the regular fashion.
Am I approaching this in the right direction? Can you please point me to the next steps?
Thanks
Richa

https://lists.launchpad.net/maria-developers/msg08303.html

From a user right now on #maria: an index on a virtual column should be used not only when the virtual column is referenced by name, but also when the expression defining that column is used. That would mean that an index on a virtual column is equivalent to a functional index (MDEV-6017) on the expression defining that column.

Example:

CREATE TABLE t1 (c1 INT, c2 INT, c3 INT AS (c1+c2) VIRTUAL, INDEX (c3));

SELECT * FROM t1 WHERE c3=42;

SELECT * FROM t1 WHERE (c1+c2)=42;

The expectation is that both queries use the index on the virtual column. The first because the virtual column is referenced by name, the second because the virtual column is referenced by the defining expression. The index on c3 would behave like a functional index on (c1+c2)