MySQL Bugs: #9639: Index on columns with functon aplied.

Bug #9639	Index on columns with functon aplied.
Submitted:	5 Apr 2005 10:09	Modified:	6 Oct 2008 13:15
Reporter:	Jan Klopper	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:		OS:	Any (any)
Assigned to:		CPU Architecture:	Any

Description:
Id would be great to have the ability to add index on columns with a function aplied.

This way we could for example create an index on month(datetimefield),year(datetimefield) and have this indexing done in the database instead of needing to rewrite the queries in the app.

Since normally fields with a function aplied don't use indexes, it would be fairly simpl to include this behaviour in the engine right?
On insert/update the index should simply be build with the function aplied.
And on select the month(datetimefield) matches the indexed column.

This is especialy handy for dates and date ranges, i think it will also speed up many mysql based apps who work with things like statistics etc. (thus giving credit to mysql for an increase in speed made possible by a simple addition of an index)

How to repeat:
Try and create a functioned index.

Suggested fix:
Add code to the alter table command to allow for some functions, (month,year) Not (sum/avg), (only the ones who aply to only one field)

Add code to te index writer, to perform the function stated in the index and store the result.
Add code to the select engine to match function'ed fields to functioned index fields if possible.

The functions on which this should be possible are functions that are deterministic (eg who give the same result very time, for the field in question)

Since we can add a length to an index to only index the first x chars, this becomes obsolete if you can use substr like functions on the data, which would also allow for substrs on the end or center of the string.

There are so many projects out there (drupal, confluence, ..) that are/were faced with performance issues and are forced to come up with workarounds in order to get decent performance from MySQL. All of this just because a simple thing like "SELECT id FROM users WHERE lower(username) = ?" can't use db index created on the username column.

Postgres and some other major dbms support indexing of functions, please make MySQL belong to the group of dbms that doesn't give developers headaches because of this.

This is a duplicate of bug #17496.