Query works fast:
select SQL_NO_CACHE
users.id, users.name
from useres
left join user_statistics
on users.id = user_statistics.user_id
where users.is_deleted = 0
group by users.id
order by users.updated_at
limit 50;
Slow Query:
select SQL_NO_CACHE
users.id, users.name,
max(user_statistics.access_time) as user_access_time
from useres
left join user_statistics
on users.id = user_statistics.user_id
where users.is_deleted = 0
group by users.id
order by user_access_time
limit 50;
Explain plan of slow query
> *************************** 1. row ***************************
> id: 1 select_type: SIMPLE
> table: users
> type: ref possible_keys: fk_users_is_deleted
> key: fk_users_is_deleted
> key_len: 4
> ref: const
> rows: 107696
> Extra: Using where; Using temporary; Using filesort
> *************************** 2. row ***************************
> id: 1 select_type: SIMPLE
> table: user_statistics
> type: ref possible_keys: fk_user_statistics_user_id
> key: fk_user_statistics_user_id
> key_len: 4
> ref: users.id
> rows: 1
> Extra:
I am using group by and order by on different tables. How can I optimize the above slow query?
left join user_statistics
is redundant and only the first table is needed.