Re: Query take too long time - please help!

2012-07-10 Thread Ananda Kumar 
can u show the explain plan for your query

On Tue, Jul 10, 2012 at 2:59 PM, Darek Maciera darekmaci...@gmail.comwrote:

 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

Re: Query take too long time - please help!

2012-07-10 Thread Ananda Kumar 
you are using a function-LOWER, which will not make use of the unique key
index on ksd.
Mysql does not support function based index, hence your query is doing a
FULL TABLE scan and taking more time.

On Tue, Jul 10, 2012 at 4:46 PM, Darek Maciera darekmaci...@gmail.comwrote:

 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