RE: Fwd: Query take too long time - please help!

2012-07-16 Thread Rick James 
If the collation for ksd in ..._ci, then it is case-insensitive, and you can 
get rid of both calls to LOWER().

 -Original Message-
 From: Carsten Pedersen [mailto:cars...@bitbybit.dk]
 Sent: Tuesday, July 10, 2012 11:22 AM
 To: Darek Maciera
 Cc: mysql@lists.mysql.com
 Subject: Re: Fwd: Query take too long time - please help!
 
 On 10.07.2012 13:16, Darek Maciera wrote:
  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
  mysql LOWER(ksd)=LOWER('4204661375');
 
 That's definitely not the query you showed the first time around. The
 query you're showing here will force a table scan to calculate
 LOWER(ksd) for every single row.
 
 Also, how do you know that ksd id unique (as stated in your original
 post)? You have no index on it to ensure uniqueness.
 
 You'll have to find some other way to query the table.
 
 Best,
 
 / Carsten
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


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

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

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

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

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

Re: Fwd: Query take too long time - please help!

2012-07-10 Thread Carsten Pedersen 

On 10.07.2012 13:16, Darek Maciera wrote:

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');


That's definitely not the query you showed the first time around. The 
query you're showing here will force a table scan to calculate 
LOWER(ksd) for every single row.


Also, how do you know that ksd id unique (as stated in your original 
post)? You have no index on it to ensure uniqueness.


You'll have to find some other way to query the table.

Best,

/ Carsten

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