Add support for Indexes on Expressions

Description

First, I apologize if this is the wrong section (or issue type/etc) since it's my first MariaDB bug.

In PostgreSQL, as well as in many other SQL DBs, is possible to create indexes on the result of function instead of real columns ( the doc for PostgreSQL is http://www.postgresql.org/docs/8.4/static/indexes-expressional.html).

Why I think is important?
A lot of times, we have to deal with databases that are not perfect, since they are the result of years of development, and we can not change them because it would mean breaking stuff. Also, sometimes is pointless to add a new column to hold these data, since they would be replicated.

Let me create a couple of examples:

Example A:
Table A with many fields, including: Year (int), Week (int)
Table B with many fields, including: Year (int), Week (int)
Table C with many fields, including: YearWeek (int)
We need to Join sometimes A with B and sometimes B with C. As you can notice, A with B is an easy Join, while B with C would require something like (Year*100+Week). This would kill the query performance since there is no way to index it.

Example B:
A table has the column "client" CHAR(100) that needs to be searched as '%STRING%'. A simple way to index this is to create a second column ("client_reverse") that is updated by trigger and contains the reverse string". Indexing "client" and "client_reverse" is possible to create a filter WHERE "client" like "STRING%" AND "client_reverse" like "GNIRTS%". This can work but becomes quickly unsustainable.

Being able to create an index on expression would solve both cases since first case would be solved by indexing (year*100+week), while the second would be solved by indexing (reverse("client")).

Aside these cases, there are a lot of other possible usage.

Thoughts?

Environment

None

Status

Assignee

Unassigned

Reporter

Fabio Alessandro Locati

Labels

None

External issue ID

None

External issue ID

None

Time tracking

160h

Fix versions

Priority

Minor
Configure