MySQL：连接和连接在 ORDER BY 查询中使用索引
我有一个类似于 stackoverflow 的问答功能的系统。主要区别在于每个问题都有一个过期日期时间：
CREATE TABLE questions (
id INT NOT NULL AUTO_INCREMENT,
title CHAR(100) NOT NULL,
details TEXT
PRIMARY KEY (id)
)
CREATE TABLE answers (
id INT NOT NULL AUTO_INCREMENT,
question_id INT NOT NULL
details TEXT
expiration_datetime DATETIME
score INT
PRIMARY KEY (id)
)
我想显示一个问题以及按分数排序的前 N 个未过期答案。我在想这样的事情：
SELECT * FROM questions, answers
WHERE questions.id=1 AND questions.id=answers.question_id AND answers.expiration_datetime > NOW()
ORDER BY answers.score DESC LIMIT 10
几个问题：
1）上面的查询是执行我想要的操作的最有效方法吗？它与显式使用 JOIN 有什么不同，例如：
SELECT * FROM questions JOIN answers ON questions.id=answers.question_id
WHERE questions.id=1 AND answers.expiration_datetime > NOW()
ORDER BY answers.score DESC LIMIT 10
2）如何使我的查询使用索引？我正在考虑将此索引添加到表答案中：
INDEX (question_id, expiration_datetime, score)
上述索引是否适用于我的查询？这似乎不对，因为点到期日期时间是升序的，而我需要分数是降序的。我在这里能做什么？
I have a system that's similar to the Q&A feature of stackoverflow. The main difference is hat each question has an expiration datetime:
CREATE TABLE questions (
id INT NOT NULL AUTO_INCREMENT,
title CHAR(100) NOT NULL,
details TEXT
PRIMARY KEY (id)
)
CREATE TABLE answers (
id INT NOT NULL AUTO_INCREMENT,
question_id INT NOT NULL
details TEXT
expiration_datetime DATETIME
score INT
PRIMARY KEY (id)
)
I want to display a question together with the top N non-expired answers ordered by score. I'm thinking something like this:
SELECT * FROM questions, answers
WHERE questions.id=1 AND questions.id=answers.question_id AND answers.expiration_datetime > NOW()
ORDER BY answers.score DESC LIMIT 10
A few questions:
1) Is the query above the most efficient way to do what I want? How is it different from explicitly using JOIN such as:
SELECT * FROM questions JOIN answers ON questions.id=answers.question_id
WHERE questions.id=1 AND answers.expiration_datetime > NOW()
ORDER BY answers.score DESC LIMIT 10
2) How to make my query use index? I'm thinking adding this index to TABLE answers:
INDEX (question_id, expiration_datetime, score)
Would the above index work for my query? It doesn't seems right because expiration_datetime is ascending while I need score to be descending. What can I do here?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
使用 INNER JOIN 或 FROM 子句连接表会给出相同的执行计划。然而，前者更容易阅读（这是自 1992 年以来的标准所建议的）。
只要有可能，您的 RDBMS 将使用索引。打印执行计划通常是个好主意（
EXPLAIN SELECT * FROM -- ...）。但是，请注意，创建多列索引可能很棘手。在您的情况下，您可以在 Question_id 上使用索引，但不能在expiration_datetime 或 Score 上使用索引，因为它们不是索引的第一列。您必须创建三个不同的索引。PS 不推荐使用 SELECT * 。始终键入您需要的列（易于阅读和重复阅读），并且仅提及您将使用的列。例如，如果您从未在脚本中使用此列，则没有必要选择初始日期！
Joining tables with INNER JOIN or in the FROM clause gives the same execution plan. However, the former is much more easy to read (and that's what standard suggest since 1992).
Your RDBMS will use an index whenever it's possible. It's usually a good idea to print the execution plan (
EXPLAIN SELECT * FROM -- ...). However, note that creating multiple columns index can be tricky. In your case, you can use the index on question_id but not on expiration_datetime or score because they aren't the first columns of the index. You would have to make three different index.P.S. SELECT * isn't recommanded. Always type the columns you need (easy to read and re-read) and only mention the columns you will use. No point to select, for instance, initial_date if you never use this column in your script!