create index in a field that contains a function

[gjuan]

Hi,

I have read some topics and in mysql is not posible to create an index when you use a function in the field that you want to filter.

For example:

SELECT field1 FROM tabla1 WHERE UPPER(field2)=‘VALOR2’ and
UPPER(field3)=‘VALOR3’ AND UPPER(field4)=‘VALOR4’ ORDER BY field5

You can notice that in this sentence a UPPER function is in use, so there is not option to create a index.

The only thing that i did is to create a index for field5 and now mysql use the index to return the results

is there another option to optimize this type of query without modify it ?

Thanks for your help.

[sterin]

If you have normal collation you can just drop the UPPER() since string comparisons are case-insensitive by default.

Ref: Case Sensitivity in String Searches

[gjuan]

[B]sterin wrote on Tue, 31 August 2010 03:48[/B]

If you have normal collation you can just drop the UPPER() since string comparisons are case-insensitive by default. Ref: Case Sensitivity in String Searches

Thanks for your answer.