MySQL-选择+加入+按性能排序
我有两个表，我需要从两个表中选择一些连接的数据
SELECT f.*
FROM file_data f
JOIN subscriptions s ON f.uid = s.elementid
WHERE s.uid = 119762 AND f.private=0
ORDER BY f.date DESC
现在，即使对于一小部分数据，查询也需要一秒钟以上的时间。 这是由于“订阅”上使用了“文件排序”和“临时”，由 ORDER BY f.date 引起（删除该条件会导致时间降至 0.01 秒以下）
谁能告诉我如何加快查询速度？
这是 EXPLAIN 的结果
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE s ref uid_elementid uid_elementid 4 const 171 Using index; Using temporary; Using filesort
1 SIMPLE f ref uid_uname uid_uname 5 s.elementid 22 Using where
I'm having two tables, I need to select some data joined from both of them
SELECT f.*
FROM file_data f
JOIN subscriptions s ON f.uid = s.elementid
WHERE s.uid = 119762 AND f.private=0
ORDER BY f.date DESC
Now, even for a small set of data the query takes over a second.
This is due to 'filesort' and 'temporary' used on "subscriptions", caused by ORDER BY f.date (removing that condition causes the time to drop under 0.01s)
Can anyone tell me how to speed up the query?
Here's the result of EXPLAIN
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE s ref uid_elementid uid_elementid 4 const 171 Using index; Using temporary; Using filesort
1 SIMPLE f ref uid_uname uid_uname 5 s.elementid 22 Using where
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
索引何时有助于排序依据是很难预测的。它很少像“按
order by对字段进行索引”那么简单。 此链接包含详细信息。如果您认为自己有正确的索引，而优化器只是不决定使用它们，请使用 索引提示，向数据库服务器建议索引。When an index will help an order by is non-trivial to predict. It is very rarely as simple as "index the fields in the
order by". This link has the nitty-gritty details. If you think you have proper indexing and the optimizer just isn't deciding to use them, use an index hint to suggest the index to the database server.你应该在 f.date 上放置一个索引
You should put an index on f.date
我遇到了同样的问题，这是我的解决方案：
I met the same problem,this is my solution: