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.