Using temporary, using filesort a bad idea in mysql?

Question

Hi all. I am trying to optimize my mysql queries to avoid 'using temporary, using filesort'. I could use some help. First; here is the explain

Here is the Query

select pf.*,m.login,m.avatar 
from profile_friends pf, members m  
where pf.friend_id = m.id and pf.member_id = 16586 
order by m.lastLogin desc 
limit 0,24;


mysql> EXPLAIN select pf.*,m.login,m.avatar from profile_friends pf, members m  where pf.friend_id = m.id and pf.member_id = 16586 order by m.lastLogin desc limit 0,24;
+----+-------------+-------+--------+-----------------------------------------------------+-----------------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                       | key             | key_len | ref                      | rows | Extra                                        |
+----+-------------+-------+--------+-----------------------------------------------------+-----------------+---------+--------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | pf    | ref    | member_id_index,friend_id_index                     | member_id_index |       4 | const                    |  160 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | m     | eq_ref | PRIMARY,member_id_privacy_index,id_last_login_index | PRIMARY         |       4 | mydb.pf.friend_id        |    1 | Using where                                  |

There are 2 tables involved. ProfileFriends (pf), and Members (m). This query is just trying to find the 'recent' 24 friends for this particular member id. Recent means sort by LastLogin date.

Thanks

Answer 1

It is a problem? Yeah.

Is it a problem when you're dealing with 160 rows? Nope.

"Filesort" is a method, not the actual creation of a file and sorting it. If we were talking about 160,000 rows instead of 160 rows, then there'd probably be reason to consider further optimizations.

Edit: Also, you omitted the actual query running time. You're hitting indexes and only working with a handful of rows. If this query is taking more than a fraction of a fraction of a second, it's probably not worth even looking at for optimization.

Answer 2

That's the most efficient way to write that query.

Make sure that pf.friend_id, pf.member_id, and m.id have indices on them. Then it will use the indices to join the tables and filter the results.

That sort is going to come up, anyway, because of your order by.