This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-16. For the latest, interactive version please visit https://mariadb.com/kb/.

Index/optimizations in Dynamic Columns

Greetz to all...

I have been playing with the dynamic columns in MariaDB 5.3...

Running your test queries I decided to do an explain on one of them:

MariaDB [test]> explain SELECT name FROM t1 WHERE COLUMN_GET(dynstr, 1 as char(10)) = "red";
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

I was just curious, do dynamic columns suffer from the same perf penalties at scale that non-indexed or function-based where criteria do?

If they currently do, are there currently any plans to index/optimize dynamic columns for super fast at-scale data access?

Best,

Joshua

Answer Answered by Michael Widenius in this comment.

Yes, currently dynamic columns can't be easily indexed. You 'can' do an index with the help of a virtual column, but that is not that convenient as you then duplicate the information for that column.

Adding indexes for dynamic columns is something we plan to do if the usage of dynamic columns picks up and there is a demand for it. MWL#215 was created for this task.

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.