List:General Discussion« Previous MessageNext Message »
From:Gavin Towey Date:August 12 2009 1:19am
Subject:RE: Slow performance Query
View as plain text  
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:dnelson@stripped]
Sent: Tuesday, August 11, 2009 1:09 PM
To: Tachu(R)
Cc: mysql@stripped
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
        dnelson@stripped

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


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.
Thread
Slow performance QueryTachu®11 Aug
  • Re: Slow performance Querymos11 Aug
  • Re: Slow performance QueryDan Nelson11 Aug
    • RE: Slow performance QueryGavin Towey12 Aug