Take the 2-minute tour ×

I have the following DB structure on my MySQL 5.1.66:

ID         int(11) PRIMARY
CID        varchar(255) INDEX
V1         varchar(1500)
DATE       datetime
IP_ADDR    varchar(255)
V2         varchar(1000)
USER_DELAY int(11)
M_ID       int(22)
REFERER    varchar(255)
BRAND      varchar(255)
LANG       varchar(255)
USER       varchar(255)

I have almost 9,000,000 rows in the DB and here are some of my more important my.cnf properties:

innodb_buffer_pool_size = 256M
innodb_log_buffer_size = 16M
key_buffer_size = 128M
bulk_insert_buffer_size = 128M
max_heap_table_size = 128M
sort_buffer_size = 8M
tmp_table_size = 128M

The problem is, when running a simple query such as:

SELECT * FROM table WHERE CID = 123456

It takes over 60 seconds to get a response (CID is an indexed field and the query has only 10 results).

EXPLAIN output for the above query:

|  1 | SIMPLE      | table | ALL  | CID           | NULL | NULL    | NULL | 9193357 | Using where |

EDIT: I just noticed something, when I place the value under quotes, I get the results in 0.13 seconds, but if I run the query without the quotes it takes 60 seconds, what causes this?

share|improve this question
Show us the complete indexes as well as an EXPLAIN of your statement. –  FreshPrinceOfSO Jan 27 '13 at 20:40
@njk please see my last edit for the EXPLAIN My only INDEX is the CID and my PRIMARY is ID. –  hoverhand Jan 27 '13 at 20:43
I think 60 sec. is so many. Try rebuild index. –  Hamlet Hakobyan Jan 27 '13 at 20:43
How many rows returns query? Also can helped if you show index statistics. –  Hamlet Hakobyan Jan 27 '13 at 20:46
@HamletHakobyan please see my last edit –  hoverhand Jan 27 '13 at 20:53
show 2 more comments

2 Answers

up vote 3 down vote accepted

If you place the value under quotes server will use index if there are no quotes i.e. integer value passed in WHERE clause and index won't works. You think 123456 will be converts to varchar and then index must works. No. The precedence of integer higher than varchar then all CID values will be converted to integer to compare and you have table scan.

share|improve this answer
add comment

The type of CID is important. If you want to use the index correctly, you should build the query with the correct type In your case, CID is varchar. Therefore it is expected that you give a value in quotes. Otherwise it will be converted into integer which takes lots of time.

share|improve this answer
add comment

Your Answer


