Forum Moderators: open

Message Too Old, No Replies

Please help to optimize query

to avoid USING TEMPORARY and USING FILESORT

         

gcan

7:50 pm on Aug 12, 2009 (gmt 0)

10+ Year Member



Is there any way 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;


+----+-------------+----------------------+--------+----------------------+------------+---------+-----------------------------------------+------+---------------------------------+
¦ 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)

Thanks.