What effect do quotes have when placed around numbers in SELECT statements?

Question

I was under the impression it didn't matter whether numbers were quoted or not, but when I noticed that this simple query:

SELECT id FROM table t WHERE t.col = 1234

Took 0.21s to execute (where id is a BIGINT primary key and col is a varchar with an index), I knew something wasn't right.

After fiddling around a while, I tried putting quotes around the number:

SELECT id FROM table t WHERE t.col = "1234"

The execution time dropped to 0.046s.

Is this a fluke, or does it matter if numbers are quoted?

EDIT: Also, how does this affect PDO queries where parameters are bound?

EDIT 2: Apparently the query plans are different:

Without quotes:

+----+-------------+-------+------+------------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys    | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+------------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | t     | ALL  | ind_col          | NULL | NULL    | NULL | 99431 | Using where |
+----+-------------+-------+------+------------------+------+---------+------+-------+-------------+

With quotes:

+----+-------------+-------+------+------------------+------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys    | key              | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+------------------+------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | t     | ref  | ind_col          | ind_col          | 767     | const |    1 | Using where |
+----+-------------+-------+------+------------------+------------------+---------+-------+------+-------------+

When you give the query a number, it takes time to convert it to a string before doing the comparison. — ziGi, Dec 16 '14 at 01:59
Looks like it has already been answered http://stackoverflow.com/questions/6781976/mysql-quote-numbers-or-not — Muggles, Dec 16 '14 at 02:03
this is the expected behavior then. you cannot use a string type index to help search for a numeric type. mysql will have to do a full table scan. it will however work in the reverse case. — Daniel Williams, Dec 16 '14 at 02:21

score 2 · Accepted Answer · edited May 23 '17 at 10:28

In terms of performance they do not matter for mysql. For when to use them you can see this question When to use single quotes, double quotes, and backticks in MySQL

You are most likely experiencing a warmed up buffer pool or query caching. Also, when I locally query a remote a sql machine in amazon I can have variability in response time just from my own network performance and remote machine load. If you want to really test it out try profiling the query.

http://dev.mysql.com/doc/refman/5.5/en/show-profile.html

For this specific case it is an eccentricity of mysql that you cannot use a string type index when querying for a numeric value. The reverse however works.

What effect do quotes have when placed around numbers in SELECT statements?

1 Answers1