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
[email protected]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
