Don’t Quote Your Numbers
March 3, 2009 · Posted in MySQL · 2 Comments
It’s a fairly simple rule, and something that should be obeyed for your health and sanity.
There are a couple of bugs which you could run into, when quoting large numbers. First of all, Bug #34384. This is concerning quoting large INTs in the WHERE condition of an UPDATE or DELETE. It seems that this will cause a table scan, which is going to be slooooow on big tables.
Similarly, there is the more recently discovered Bug #43319. You can run into this if you quote large INTs in the IN clause of a SELECT … WHERE. For example:
mysql> EXPLAIN SELECT * FROM a WHERE a IN('9999999999999999999999')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE TABLE: NULL type: NULL possible_keys: NULL KEY: NULL key_len: NULL ref: NULL rows: NULL Extra: Impossible WHERE noticed after reading const TABLES 1 row IN SET (0.00 sec) mysql> EXPLAIN SELECT * FROM a WHERE a IN('9999999999999999999999', '9999999999999999999999')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE TABLE: a type: ALL possible_keys: PRIMARY KEY: NULL key_len: NULL ref: NULL rows: 655360 Extra: USING WHERE 1 row IN SET (0.00 sec)
Note that you only run into it when you quote multiple large numbers.
Anyway, the long and the short of this post is: if at all possible, don’t quote numbers. MySQL will love you for it.
-
Gary Pendergast, noun: Gamer and developer. MySQL, WordPress, web and music geek. Currently employed as a MySQL Support Engineer for Oracle. Can be observed in his natural habit at pento.net, or usually in Melbourne, Australia.
