CODEWITHSUNDEEP
mysqlinnodb
Or Weinberger
Or Weinberger

Reputation: 7482

MySQL InnoDB performance issues

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:

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  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?

Upvotes: 0

Views: 219

Answers (2)

Kayser
Kayser

Reputation: 6704

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.

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33391

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.

Upvotes: 3

Related Questions