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=arch...@jab.org
