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=arch...@jab.org