why index refuse to work??

We never thought primary key refuse to work in a query even if the key included in where clause. yes, it happens. I got a a mail from developer team saying a very simple query is taking minutes to fetch the records although it should take milliseconds. initially i execute the query and check with explain.

mysql> explain select * from ad_detail where ad_id=5029;
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | ad_details | ALL  | PRIMARY       | NULL | NULL    | NULL | 3926237 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

ad_id is primary key, query should use this key. however, it not happening here. where is the problem?

lets check the table structure.

mysql> desc ad_details;
+------------------+----------------+------+-----+-------------------+-----------------------------+
| Field            | Type           | Null | Key | Default           | Extra                       |
+------------------+----------------+------+-----+-------------------+-----------------------------+
| AD_ID            | varchar(50)    | NO   | PRI | NULL              |                             |
| SA_ID           | varchar(50)    | YES  | MUL | NULL              |                             |
| BOOKING_C   | varchar(20)    | YES  | MUL | INT               |                             |
| CT_ID           | varchar(10)    | YES  |     | NULL              |                             |
| SB_CT_ID       | varchar(10)    | YES  |     | NULL              |                             |
| AD_TITLE         | varchar(110)   | YES  |     | NULL              |                             |
| AD_DESCRIPTION   | varchar(10100) | YES  |     | NULL              |                             |
| EMAIL            | varchar(50)    | YES  |     | NULL              |                             |
| MOBILE           | varchar(15)    | YES  |     | NULL              |                             |
| LAND_LINE        | varchar(20)    | YES  |     | NULL              |                             |
| AD_POST_DATE     | date           | YES  |     | NULL              |                             |
| AD_EXPIRY_DATE   | date           | YES  |     | NULL              |                             |
-----------------------------------------------------------------------------------------------

wow!!! here is the problem, ad_id is varchar. comparing string/varchar to integer not going to use the index ad_id. put the ad_id value in " "

change the query to select * from ad_detail where ad_id="5029";

lets the explain output again

mysql> explain select * from ad_detail where ad_id="5029";
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | ad_details | const | PRIMARY       | PRIMARY | 52      | const |    1 |       |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+----

Now we can see the query is using PRIMARY as key and just scanning 1 record to execute it.
This is very simple mistake we sometimes do referring a varchar to integer value in code/application.

I think for better utilization of index we should keep primary key as integer rather than a varchar.