0

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

1
  • Provide complete CREATE TABLE for this table before your attempt to index the column and the index creation query which causes an issue.
    – Akina
    Commented Feb 15 at 11:13

1 Answer 1

2

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)

3
  • Thank you for your insight. I thought it might be a bug or not implemented in Aria.
    – Bjarne
    Commented Feb 15 at 11:36
  • STORED INVISIBLE seems to work, so that might be a work around for you Commented Feb 15 at 11:39
  • @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).
    – Bjarne
    Commented Feb 15 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.