Hello,
Is there any way hot to avoid Filesorts and Using Temporary in this query?
Indexed columns are:
stats_profileviewers.viewer_id,
stats_profileviewers.profile_id,
stats_profileviewers.vtime,
users_table.id
portalsession.user
SELECT
stats_profileviewers.viewer_id,
stats_profileviewers.vtime,
users_table.nick,
users_table.gender,
portalsession.user
FROM stats_profileviewers
join users_table on stats_profileviewers.viewer_id = users_table.id
left join portalsession on stats_profileviewers.viewer_id = portalsession.user
WHERE stats_profileviewers.profile_id='28603'
order by stats_profileviewers.vtime DESC limit 0, 20;
this is EXPLAIN:
+----+-------------+----------------------+--------+----------------------+------------+---------+-----------------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+--------+----------------------+------------+---------+-----------------------------------------+------+---------------------------------+
| 1 | SIMPLE | stats_profileviewers | ref | profile_id,viewer_id | profile_id | 4 | const | 6 | Using temporary; Using filesort |
| 1 | SIMPLE | portalsession | index | PRIMARY | PRIMARY | 17 | NULL | 278 | Using index |
| 1 | SIMPLE | users_table | eq_ref | PRIMARY | PRIMARY | 4 | databas2.stats_profileviewers.viewer_id | 1 | |
+----+-------------+----------------------+--------+----------------------+------------+---------+-----------------------------------------+------+---------------------------------+
3 rows in set (0.00 sec)
=================================
