What effect do quotes have when placed around numbers in SELECT statements?
I was under the impression that it didn't matter if the numbers were quoted or not, but when I noticed this simple query:
SELECT id FROM table t WHERE t.col = 1234
Took 0.21s
to execute (where id
is the primary key BIGINT
and col is varchar
with the index), I knew something was wrong.
After playing for a long time, I tried to put quotes around the number:
SELECT id FROM table t WHERE t.col = "1234"
Lead time dropped to 0.046s
.
Is this a fluke or does it matter if the numbers are quoted?
EDIT: Also how does this affect PDO queries, where are the parameters related?
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 |
+----+-------------+-------+------+------------------+------------------+---------+-------+------+-------------+
source to share
From a performance point of view, they don't matter to mysql. For using them, you can see this question When to use single quotes, double quotes and backreferences in MySQL
You are most likely experiencing a heated buffer pool or query caching. Also, when I query the remote sql machine locally in amazon, I can only have variability in response time from the native network performance and boot of the remote machine. If you want to test it, try profiling the request.
http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
In this particular case, the eccentricity of mysql is that you cannot use a row type index when querying for a numeric value. However, the opposite works.
source to share