simple SQL statement takes longer time to execute [duplicate]

Question

Possible Duplicate:
Disadvantages of quoting integers in a Mysql query?

I have a very simple table Called Device on MYSql database.

+-----------------------------------+--------------+------+-----+----------------+
| Field                             | Type         | Null | Key | Extra          |
+-----------------------------------+--------------+------+-----+----------------+
| DTYPE                             | varchar(31)  | NO   |     |                |
| id                                | bigint(20)   | NO   | PRI | auto_increment |
| dateCreated                       | datetime     | NO   |     |                |
| dateModified                      | datetime     | NO   |     |                |
| phoneNumber                       | varchar(255) | YES  | MUL |                |
| version                           | bigint(20)   | NO   |     |                |
| oldPhoneNumber                    | varchar(255) | YES  |     |                |
+-----------------------------------+--------------+------+-----+----------------+

This table has more than 100K records. I am running a very simple query

select * from AttDevice where phoneNumber = 5107357058;

This query takes almost 4-6 second, But when I change this query a little bit as shown below.

select * from AttDevice where phoneNumber = '5107357058';

It takes almost no time to get executed. Notice that phoneNumber column is varchar. I don't understand why the former case takes longer time and later doesn't. The difference between these two queries is the single quote. Does MYSQL treats these to query differently if so then why?

EDIT 1

I used EXPLAIN and got the following output but don't know how to interpret these two results.

mysql> EXPLAIN select * from AttDevice where phoneNumber = 5107357058;
+----+-------------+-----------+------+---------------------------------------+------+---------+------+---------+-------------+
| id | select_type | table     | type | possible_keys                         | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+------+---------------------------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | Device    | ALL  | phoneNumber,idx_Device_phoneNumber | NULL | NULL    | NULL | 6482116 | Using where |
+----+-------------+-----------+------+---------------------------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN select * from AttDevice where phoneNumber = '5107357058';
+----+-------------+-----------+------+---------------------------------------+-------------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys                         | key         | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+---------------------------------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      |   Device  | ref  | phoneNumber,idx_Device_phoneNumber    | phoneNumber | 258     | const |    2 | Using where |
+----+-------------+-----------+------+---------------------------------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Can someone explain me about the key, key_len and rows present in EXPLAIN query output?

Answer 1

1) Thank you for the "EXPLAIN". We all (including you, I'm sure) knew that the problem was that mysql had to convert the integer to a string, and had to do it for each row. But your "EXPLAIN" proved it.

2) Here's a nice, short article about EXPLAIN:

• http://www.lornajane.net/posts/2011/explaining-mysqls-explain

The *possible_keys* shows which indexes apply to this query and the key tells us which of those was actually used -... Finally the rows entry tell us how many rows MySQL had to look at to find the result set.

Search value:   key:        type:  ref:   rows:  
-------------   ---         ----   ----   ----
5107357058      NULL        ALL    NULL   6482116
'5107357058'    phoneNumber ref    const  2

3) The "ref" column is the "The columns compared to the index". In the second case, the string literal ("constant") '5107357058' was compared to the key "phoneNumber". In the first case, there was no usable key (because your search condition was a completely different type); hence "ref" was NULL.

4) The "type" column is "The join type". "Ref" means "All rows with matching index values are read from this table" (in this case, 2 rows). "ALL" mans "full table scan". Which in this case means 6 million rows.

5) Here's the mysql documentation for "EXPLAIN":

• http://dev.mysql.com/doc/refman/5.5/en/explain-output.html