Announcement

Announcement Module
Collapse
No announcement yet.

create index in a field that contains a function

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • create index in a field that contains a function

    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.

  • #2
    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

    Comment


    • #3
      sterin wrote on Tue, 31 August 2010 03:48

      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.

      Comment

      Working...
      X