Don’t Quote Your Numbers

March 3, 2009 · Posted in MySQL · 3 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.

Tags: <, , >