# create index in a field that contains a function

**URL:** <https://forums.percona.com/t/create-index-in-a-field-that-contains-a-function/1488>\
**Category:** Other MySQL® Questions\
**Created:** [August 30, 2010, 4:40pm UTC](https://forums.percona.com/t/create-index-in-a-field-that-contains-a-function/1488 "2010-08-30T16:40:07Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![gjuan](https://avatars.discourse-cdn.com/v4/letter/g/53a042/32.png) [@gjuan](https://forums.percona.com/u/gjuan)\
**Post date:** [August 30, 2010, 4:40pm UTC](https://forums.percona.com/t/create-index-in-a-field-that-contains-a-function/1488/1 "2010-08-30T16:40:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [August 31, 2010, 2:18am UTC](https://forums.percona.com/t/create-index-in-a-field-that-contains-a-function/1488/2 "2010-08-31T02:18:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![gjuan](https://avatars.discourse-cdn.com/v4/letter/g/53a042/32.png) [@gjuan](https://forums.percona.com/u/gjuan)\
**Post date:** [August 31, 2010, 12:32pm UTC](https://forums.percona.com/t/create-index-in-a-field-that-contains-a-function/1488/3 "2010-08-31T12:32:03Z")

</div>

| [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.