List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:August 11 2009 8:09pm
Subject:Re: Slow performance Query
View as plain text  
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
	dnelson@stripped
Thread
Slow performance QueryTachu®11 Aug
  • Re: Slow performance Querymos11 Aug
  • Re: Slow performance QueryDan Nelson11 Aug
    • RE: Slow performance QueryGavin Towey12 Aug