MySQL update does not use index when using large quoted integer in WHERE

Question

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

When I execute the following query with a very large QUOTED integer in WHERE, it does not use the PK index and is very slow (it takes a few minutes):

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

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

 update BIG_TABLE set some_value=0 where id=10000000000; 

If I do not use a large integer value, the query is executed quickly even with quotation marks:

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

So, it looks like MySQL is not using the index for the integer column when the value in WHERE cannot be converted to integer (the value is too large for the integer). Is there a workaround for this?

I need a workaround from MySQL. Since this request is created by some closed perl library, which cannot be modified. All WHERE arguments are quoted automatically, and it sometimes happens that the Update query is built with a very large integer (this is an illegal value, so I just expect UPDATE to not update anything).

What is currently happening is that this update request runs for several minutes and it displays the whole system.

Note that the selection works correctly even when quoting large integers. No problem with this:

 select * from BIG_TABLE where id='10000000000'; 

This is something with UPDATE.

Any ideas? Thanks!

Answer 1

You can 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;

Answer 2

Thank you for your responses.

Well, I have not found a solution from MySQL. It seems like some MySQL errors - I changed the code to avoid calling the Perl library (which builds the query) with large values.

Thanks!