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:
Category:MySQL Server: General Severity:S4 (Feature request)
Version: OS:Any (any)
Assigned to: CPU Architecture:Any

[5 Apr 2005 10:09] Jan Klopper
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.
[5 Apr 2005 11:45] Jan Klopper
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.
[1 Jul 2008 3:04] Igor Minar
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.
[6 Oct 2008 13:15] Valeriy Kravchuk
This is a duplicate of bug #17496.