使用临时，使用Filesort在mysql中是个坏主意? [英] Using temporary, using filesort a bad idea in mysql?

问题描述

我正在尝试优化我的mysql查询，以避免使用临时文件，使用文件排序".我可以帮忙.第一的;这是解释

I am trying to optimize my mysql queries to avoid 'using temporary, using filesort'. I could use some help. First; here is the 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;


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                                  |

涉及2个表. ProfileFriends(pf)和Members(m).该查询正试图为此特定会员ID查找最近"的24个朋友.最近的均值按LastLogin日期排序.

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.

谢谢

推荐答案

这是问题吗?是的.

当您处理160行时，这是一个问题吗?不.

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

文件排序"是一种方法，而不是文件的实际创建和排序.如果我们谈论的是160,000行而不是160行，那么可能有理由考虑进一步的优化.

"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.

另外，您省略了实际的查询运行时间.您要命中索引，并且只能处理少量的行.如果此查询花费的时间不超过一秒的几分之一，那么即使进行优化也可能不值得.

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.

这篇关于使用临时，使用Filesort在mysql中是个坏主意?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！