Hi,
thanks for the input. This seems to work. But I am getting this
error:
MySQL Error 1206
The total number of locks exceeds the lock table size
Search around the net, I found references to increase the
innodb_buff_cache size. I am not sure how this will help. But I am
wondering if there is an option in MySQL to perform a chunksize
commit; like commit every 50 records that has been updated. Is there
such an option?
On Nov 7, 4:56 pm, da404lewzer <[EMAIL PROTECTED]> wrote:
> anytime you are storing data to mysql, it's best to set the data how
> you will need it. you might try adding a second uid column (uid_up)
> and pre-format the data. You can still get the 'unmodified' data from
> uid, and use the pre-formatted (upper) data so mysql doesn't have to
> do a full scan. I would add a second uID column (uID_up) that already
> has the data as uppercase. plus, this column can have a key.
>
> First, make another column named uid_up. You can have mysql quickly
> populate the correct values with:
>
> UPDATE tablename SET uid_up = upper(uid) WHERE 1
>
> Next, modify all your INSERT queries to then do the following:
>
> INSERT INTO tablename (uid, uid_up, ...) VALUES ( '$uid',
> upper('$uid'), ... )
>
> or even:
> $uid_up = strtoupper($uid);
> INSERT INTO tablename (uid, uid_up, ...) VALUES ( '$uid',
> '$uid_up', ... )
>
> Finally, your SELECT queries need to be modified
>
> SELECT * FROM tablename t, tablename2 p, ... WHERE p.uid_up =
> t.uid_up ....
>
> Another (crappy) approach might be to modify your SELECT to use LIKE
> instead of equals
>
> p.uid LIKE t.uid ....
>
> LIKE is case insensitive (and somewhat slow) but still may require a
> full scan....
>
> Hope this helps :D
>
> On Nov 6, 8:38 am, 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.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
