MySQL Update doesn't use index when using large quoted integer in WHERE

Question

I have a large table (MyISAM) with id as primary key (MySQL version 5.1.54).

When I perform the following query with very large QUOTED integer in WHERE, it doesn't use PK index and runs very very slow (takes several minutes to complete):

update BIG_TABLE set some_value=0 where id='10000000000';

If I remove quotes, query runs very fast (PK Index is used correctly). This one runs fast:

update BIG_TABLE set some_value=0 where id=10000000000;

If I don't use large integer value, query runs fast even with quotes:

update BIG_TABLE set some_value=0 where id='100';

So, it looks like MySQL fails to use index on integer column when value in WHERE can't be converted to integer (value too big for integer). Is there any workaround for this?

I need workaround from MySQL side. Because this query is build by some closed perl library, which can't be changed. All WHERE arguments are quoted automatically and sometimes it happens that Update query is built with very large integer (it's illegal value, so I just expect UPDATE not to update anything).

Right now what happens is that this update query is performed several minutes and it brings the whole system down.

Please note that select is working correctly even with large integers quoted. No problems with this one:

select * from BIG_TABLE where id='10000000000';

It's something with UPDATE.

Any ideas? Thanks!

Answer 1

Can you try with this..

update BIG_TABLE as B1,BIG_TABLE as B2 set B1.some_value=0 where B2.id='10000000000' AND B1.ID = B2.ID;