Aryo, I believe any form of function such as like, upper, lower will cause mysql to ignore the index.
On Nov 16, 2:08 pm, "Aryo Sanjaya" <[EMAIL PROTECTED]> wrote: > Rather than using '=' operator, why don't you use 'like' instead? it's > case insensitive. > > Where p.uid like t.uid > > On 11/6/08, tekion <[EMAIL PROTECTED]> wrote: > > > > > > > Folks, > > I am running into a situation on query using and index field on uid > > column. When I run a query using where clause like "p.upper(uid) = > > t.upper(uid)", the query seems to do a fullscan of the table. It > > seems that Mysql ignore the index when I use upper function in the > > where clause. I have tried to create the index using the upper > > function like so: "alter table <tablename> add index (upper(uid))", > > but it seems I can't do this either. > > > One of the solution is to alter the uid to upper case as it is being > > inserted into the tables. But If one of the table is holding auditing > > data,: there is a requirement not to alter the uid (capturing what > > the user is actually entering), then I can't alter the uid. Does any > > one have a better solution on how to index the uid on this table so > > the query would use the index instead of ignoring it due to the use of > > upper case function? Thanks in advance. > > -- > Sent from my mobile device > > http://www.mahesajenar.com --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "PHP & MySQL" group. To post to this group, send email to [EMAIL PROTECTED] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/phpmysql?hl=en -~----------~----~----~----~------~----~------~--~---
