Slow performance Query

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=gcdmg-mysql-2@m.gmane.org

Re: Slow performance Query

It is slow probably because you are using

where user_id=3D'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=AE 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=3D'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;
>+----------=20
>+---------------------------------------------------------- ----------------=
------------------------------------------------------------ ----------------=
------------------------------------------------------------ ----------------=
------------------------------------------------------------ ----------------=
------------------------------------------------------------ ----------------=
------------------------------------------------------------ ----------------=
------------------------------------------------------------ ----------------=
-------------------------------------+
>| Table | Create
>Table=20
>|
>+----------=20
>+---------------------------------------------------------- ----------------=
------------------------------------------------------------ ----------------=
------------------------------------------------------------ ----------------=
------------------------------------------------------------ ----------------=
------------------------------------------------------------ ----------------=
------------------------------------------------------------ ----------------=
------------------------------------------------------------ ----------------=
-------------------------------------+
>| 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=3DInnoDB AUTO_INCREMENT=3D67577494 DEFAULT CHARSET=3Dutf8 |
>+----------=20
>+---------------------------------------------------------- ----------------=
------------------------------------------------------------ ----------------=
------------------------------------------------------------ ----------------=
------------------------------------------------------------ ----------------=
------------------------------------------------------------ ----------------=
------------------------------------------------------------ ----------------=
------------------------------------------------------------ ----------------=
-------------------------------------+
>1 row in set (0.00 sec)
>
>
>mysql> explain select app_id from app_user where user_id=3D'1421767810'
>limit 3;
>+----+-------------+----------+------+---------------+----- ----=20
>+---------+-------+------+-------+
>| id | select_type | table | type | possible_keys | key |
>key_len | ref | rows | Extra |
>+----+-------------+----------+------+---------------+----- ----=20
>+---------+-------+------+-------+
>| 1 | SIMPLE | app_user | ref | user_id | user_id |
>8 | const | 5 | |
>+----+-------------+----------+------+---------------+----- ----=20
>+---------+-------+------+-------+
>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=3Dmos99@fastmail.fm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

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@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Slow performance Query

Have you tried removing the quotes from around the value in:

user_id=3D'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 caus=
ed it to take a long time.

-----Original Message-----
From: Dan Nelson [mailto:dnelson@allantgroup.com]
Sent: Tuesday, August 11, 2009 1:09 PM
To: Tachu(R)
Cc: mysql@lists.mysql.com
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 wha=
t
> could cause it here is an example. This one is taking long in the sendin=
g
> 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=3D'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=3D'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@allantgroup.com

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


The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg