List:General Discussion« Previous MessageNext Message »
From:mos Date:August 11 2009 6:44pm
Subject:Re: Slow performance Query
View as plain text  
It is slow probably because you are using

where user_id='1421767810'

and user_id is an integer. I think this is one of those d'uh moments. :-)

Mike

At 01:18 PM 8/11/2009, Tachu® wrote:
>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)
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

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