List:General Discussion« Previous MessageNext Message »
From:Tachu® Date:August 11 2009 6:18pm
Subject:Slow performance Query
View as plain text  
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

Thanks
T

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 profile;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000177 |
| Opening tables     | 0.000080 |
| System lock        | 0.000015 |
| Table lock         | 0.000018 |
| init               | 0.000030 |
| optimizing         | 0.000020 |
| statistics         | 0.000072 |
| preparing          | 0.000023 |
| executing          | 0.000014 |
| Sending data       | 1.159199 |
| end                | 0.000054 |
| query end          | 0.000016 |
| freeing items      | 0.000030 |
| logging slow query | 0.000013 |
| logging slow query | 0.000030 |
| cleaning up        | 0.000015 |
+--------------------+----------+
16 rows in set (0.00 sec)

mysql> show create table app_user;
+---------- 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create  
Table 
                                                                                          
                                                                                          
                                                                                          
                                                                                          
                                                                                          
                                                                                          
         |
+---------- 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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`),
   CONSTRAINT `app_user_ibfk_1` FOREIGN KEY (`app_id`) REFERENCES  
`app` (`app_id`) ON DELETE CASCADE,
   CONSTRAINT `app_user_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES  
`user` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=67577494 DEFAULT CHARSET=utf8 |
+---------- 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


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)



Thread
Slow performance QueryTachu®11 Aug
  • Re: Slow performance Querymos11 Aug
  • Re: Slow performance QueryDan Nelson11 Aug
    • RE: Slow performance QueryGavin Towey12 Aug