MariaDB - How to add index to a generated VIRTUAL column

Question

I am Using MariaDB 10.11.6 and have a large table (~1.4bn rows) with log data.

I am trying to add an index to a VIRTUAL column created with:

ALTER TABLE log_table ADD COLUMN Days int(11) UNSIGNED AS (TO_DAYS(LogTime)) VIRTUAL INVISIBLE;

Based on the MariaDB documentation for generated columns it should be possible, but I get the following error:

ERROR 1904 (HY000): Key/Index cannot be defined on a virtual generated column

The table is using the "Aria" storage engine, and generated columns should be supported:

InnoDB, Aria, MyISAM and CONNECT support generated columns

— Generated Columns - Storage Engine Support

Adding an index on the generated column should be supported:

Defining indexes on both VIRTUAL and PERSISTENT generated columns is supported.

— Generated Columns - Index Support

Note: The table in question is also partitioned on a datetime column, using code like VALUES LESS THAN TO_DAYS('2024-02-15') as per Rick James' guide to partition maintenance.

Could you please help me find out why I cannot create an index on the generated virtual column?

Best regards
-Bjarne

• Akina: Provide complete CREATE TABLE for this table before your attempt to index the column and the index creation query which causes an issue.

Answer 1

This seems to be an open issue: MDEV-18812, Add support for generated indexed virtual columns to aria. It was created 2019年03月04日, so I would not hold my breath in wait for a solution

For the sake of it, I created a Fiddle, MariaDB 10.9 for aria (which fails) and innodb (that succeeds)

• Bjarne: Thank you for your insight. I thought it might be a bug or not implemented in Aria.
• Lennart - Slava Ukraini: STORED INVISIBLE seems to work, so that might be a work around for you
• Bjarne: @lennart-slava-ukranini I think it's called PERSISTENT in MariaDB. It does work with persisten, but given the number of rows (1.4 billion) this will make the table a lot larger on disk. During creation of this generated column a full table scan is needed, which makes the table unavailable for a long time (4-6 hours).