Can we create functional indexes in MySql for UPPERCASE and LOWERCASE functions.
I have searched but could not find anything relevant in the internet, if some has one implemented such a thing.
Asked
Active
Viewed 7,905 times
4
Opal
- 81,889
- 28
- 189
- 210
Debashis Bhattacharyya
- 125
- 2
- 7
-
2Why would this be useful? What problem are you trying to solve? – Michael - sqlbot May 03 '15 at 01:17
-
Performance issues on an appliaction that sits on top of MySql , the code was originally written by A Mr X and it has been given to me to fix perf issues on it, was analyzing the queries and hence wanted to check if we can use Func Index for Upper queries. – Debashis Bhattacharyya May 05 '15 at 19:33
-
Why not run an `EXPLAIN` to check for the real cause of the problem? – Nico Haase Apr 01 '20 at 07:04
2 Answers
5
Yes, MySQL 8.0.13 added indexed expressions.
create index upper_col1 on my_table ( (upper(col1)) )
Schwern
- 153,029
- 25
- 195
- 336
1
As per this post it is not generally possible to create a function based index, though you could use a sub string (from beginning).
Probably the closest you can get in MySQL is, if you add another (indexed) column and use an UPDATE trigger that assigns the results of your function to it.
