Query take too long time - please help!

2012-07-10 Thread Darek Maciera 
Hello,

I have table:

mysql DESCRIBE books;

|id  |int(255)  | NO   | PRI |
NULL  | auto_increment |
| idu   | int(255)  | NO   | MUL | NULL
| ksd   | char(15)  | YES  | | NULL
| idn   | int(1)| NO   | MUL | NULL
| text1   | text  | NO   | | NULL
| ips | int(1)| NO   | MUL | NULL
| ips2| int(1)| NO   | MUL | NULL
| date | timestamp | NO   | | CURRENT_TIMESTAMP
| date2   | date  | NO   | | NULL
| text2| text  | NO   | | NULL
| text3| text  | NO   | | NULL

I have indexes in this table:

mysql SHOW INDEX FROM uzytkownicy_strona_kody;

| books |  0 | PRIMARY|1 | id  | A
|  369625 | NULL | NULL   |  | BTREE  |
|
| books |  1 | idx_idu|1 | idu  | A
 |  184812 | NULL | NULL   |  | BTREE  |
|
| books |  1 | idx_id |1 | id | A
   |  369625 | NULL | NULL   |  | BTREE  |
|
| books |  1 | idx_idn|1 | idn  | A
 |   8 | NULL | NULL   |  | BTREE  |
|
| books |  1 | idx_ips  |1 | ips  | A   |
 8 | NULL | NULL   |  | BTREE  | |
| books |  1 | idx_ips2 |1 | ips2 | A  |
8 | NULL | NULL   |  | BTREE  | |

Every books have unique: 'ksd'. There are about 370.000 records in this table.

But this SELECT take too long time:

mysql SELECT * FROM books WHERE ksd ='A309CC47B7';
1 row in set (2.59 sec)


Table is in InnoDB engine. I added to my.cnf:   innodb_buffer_pool_size = 512MB

Any suggestions? Help, please..

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Fwd: Query take too long time - please help!

2012-07-10 Thread Darek Maciera 
2012/7/10 Ananda Kumar anan...@gmail.com:
 can u show the explain plan for your query


Thanks, for reply!

Sure:

mysql EXPLAIN SELECT * FROM books WHERE LOWER(ksd)=LOWER('4204661375');
++-+-+--+---+--+-+--++-+
| id | select_type | table   | type | possible_keys |
key  | key_len | ref  | rows   | Extra   |
++-+-+--+---+--+-+--++-+
|  1 | SIMPLE  | books  | ALL  | NULL  |
NULL | NULL| NULL | 378241 | Using where |
++-+-+--+---+--+-+--++-+
1 row in set (0.00 sec)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql