Student: Alexey Mogilyovkin
GitHub: @ZeroICQ
Organization: MariaDB
Project Title: Add support for Indexes on Expressions
My task was to add support for indexes on expression. At the very beginning it was split into two steps:
- Add expression matching into the optimizer, use it for generated columns. So the following request should work and use index
CREATE TABLE t1 (a int, b int, INDEX (a/2+b));
...
SELECT * FROM t1 WHERE a/2+b=100;- Support the syntax to create an index on expression directly, this will automatically create a hidden generated column under the hood.
CREATE TABLE t(a int, b int, index((a+b/2)));As for me, one of the most challenging part of participating in GSoC was to get familiar with huge codebase. It really overwhelmed me at first. But with the help of competent mentors I started coding very quickly.
There was no quick and ready solution how to make algorithm that would rewrite request to database in such a way that it would use the best index. But after some discussion with contributors in chat the solution was found. The solution was to take where clause, rewrite expressions with corresponding fields and conjunct with source one.
At the second phase I added syntax support to create an index on expression directly. This task was quite difficult, because of a lot of internal optimizations.
But anyway, I've done almost everything I planned. After GSoC I will finish all the tasks.
- Added expression matching into the optimizer.
- Add syntax support to create an index on expression directly.
- More thorough testing.
- Implement correct display of indexes on hidden generated columns.
- Implement automatic deletion of hidden fields on corresponding fields deletion.
GitHub clone repository PR - contains code review from mentor.
GitHub MariaDB repository PR - last GSoC 2019 commit is Support the syntax to create an index on expression.
Jira task MDEV-6017
Zulipchat discussion - requires login.