Have you tried removing the quotes from around the value in: user_id='1421767810'
The column is defined as bigint. You're comparing it to a string. I just saw a case where comparing a float value to a string column in a query caused it to take a long time. -----Original Message----- From: Dan Nelson [mailto:dnel...@allantgroup.com] Sent: Tuesday, August 11, 2009 1:09 PM To: Tachu(R) Cc: mysql@lists.mysql.com Subject: Re: Slow performance Query In the last episode (Aug 11), Tachu(R) said: > Hi guys I've been having some slow performance on queries that should > otherwise be pretty fast. I've checked my indexes etc. and cant see what > could cause it here is an example. This one is taking long in the sending > data step. although its running on localhost so its not like its a > network issue. I sometimes have some queries take long in the statistics > step. Although i cannot find a reliable document that says what > statistics means. can anyone throw some help here Is the system serving a lot of other queries at the same time? On an idle system that query should take a fraction of a second. One way to speed it up would be to add another index on (user_id,app_id). That will group all the data you need together in one block in the index so mysql won't have to seek into the table at all. Your `app_id` index has the necessary columns, but your WHERE clause needs an index with user_id first so it has to fall back to the `user_id` index, which doesn't have the app_id column. > select app_id from app_user where user_id='1421767810' limit 3; > +--------+ > | app_id | > +--------+ > | 100876 | > | 46888 | > | 93166 | > +--------+ > 3 rows in set (1.16 sec) > mysql> show create table app_user; > | app_user | CREATE TABLE `app_user` ( > `app_user_id` int(10) unsigned NOT NULL AUTO_INCREMENT, > `app_id` int(10) unsigned NOT NULL, > `user_id` bigint(20) unsigned NOT NULL, > `num_sent` int(10) unsigned NOT NULL, > PRIMARY KEY (`app_user_id`), > KEY `app_id` (`app_id`,`user_id`), > KEY `user_id` (`user_id`), > > mysql> explain select app_id from app_user where user_id='1421767810' > limit 3; > +----+-------------+----------+------+---------------+---------+---------+-------+------+-------+ > | id | select_type | table | type | possible_keys | key | key_len | > ref | rows | Extra | > +----+-------------+----------+------+---------------+---------+---------+-------+------+-------+ > | 1 | SIMPLE | app_user | ref | user_id | user_id | 8 | > const | 5 | | > +----+-------------+----------+------+---------------+---------+---------+-------+------+-------+ > 1 row in set (0.01 sec) -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
