优化 MySQL 查询
我有以下 MySQL 查询
SELECT e.id, IF(c.id = 1, g.url, e.url) AS url, IF(s.id = 4, c.short, s.name) AS sub, e.title, b.name AS category
FROM g, c, e, b, s
WHERE e.category = b.id
AND e.subcategory = s.id
AND g.c = c.id
AND (g.g = e.id OR s.id != 4)
AND e.`release` < UNIX_TIMESTAMP()
GROUP BY e.id
ORDER BY e.`release` DESC
LIMIT 15
这花了大约 2.5 秒。但如果我去掉GROUP BY，大约需要1.8秒。如果我删除 ORDER BY，则需要 2.3 秒。但是如果我删除 GROUP BY 和 ORDER BY，则需要大约 0.005 秒（但会有很多重复行）。
我应该怎么做才能以更快的时间获得相同的结果？
这是查询的解释，如果有帮助的话。
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE c ALL PRIMARY NULL NULL NULL 26 Using temporary; Using filesort
1 SIMPLE g ref console console 4 int41988_leveli.consoles.id 60
1 SIMPLE e ALL PRIMARY,id NULL NULL NULL 208 Using where; Using join buffer
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 e.cat 1
1 SIMPLE s eq_ref PRIMARY PRIMARY 4 e.subcategory 1
编辑：
这是已经存在的索引。
c.id PRIMARY KEY
b.id PRIMARY KEY
e.id PRIMARY KEY
s.id PRIMARY KEY
g.id PRIMARY KEY
g.url AND g.console UNIQUE INDEX
I have the following MySQL query
SELECT e.id, IF(c.id = 1, g.url, e.url) AS url, IF(s.id = 4, c.short, s.name) AS sub, e.title, b.name AS category
FROM g, c, e, b, s
WHERE e.category = b.id
AND e.subcategory = s.id
AND g.c = c.id
AND (g.g = e.id OR s.id != 4)
AND e.`release` < UNIX_TIMESTAMP()
GROUP BY e.id
ORDER BY e.`release` DESC
LIMIT 15
This took about 2.5 seconds. But if I remove the GROUP BY, it takes about 1.8 seconds. If I remove ORDER BY, it takes 2.3 seconds. BUT if I remove GROUP BY and ORDER BY, it tooks about 0.005 seconds (but then there is many duplicate rows).
What should I do to get same results with faster time?
Here is the explain of the query, if it helps.
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE c ALL PRIMARY NULL NULL NULL 26 Using temporary; Using filesort
1 SIMPLE g ref console console 4 int41988_leveli.consoles.id 60
1 SIMPLE e ALL PRIMARY,id NULL NULL NULL 208 Using where; Using join buffer
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 e.cat 1
1 SIMPLE s eq_ref PRIMARY PRIMARY 4 e.subcategory 1
EDIT:
Here are the indexes that already exist.
c.id PRIMARY KEY
b.id PRIMARY KEY
e.id PRIMARY KEY
s.id PRIMARY KEY
g.id PRIMARY KEY
g.url AND g.console UNIQUE INDEX
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
尝试在两个字段上使用单一索引：e.id 和 e.release。或者每个字段一个索引：e.id 和 e.resource，但我怀疑它有帮助。
Try using single index on both fields: e.id and e.release. Or one index per each field: e.id and e.resourse, but I doubt it helps.
在
SELECT子句中使用DISTINCT需要多长时间？How long does it take when you use
DISTINCTin theSELECTclause?尝试将表逐个
连接，而不是将 5 个表全部连接在一起，然后使用where进行限制。从最严格的连接开始，即首先连接产生较少行的表。Try
joining the tables one by one instead of the 5 of them all together and then restricting it withwhere. Start with most restrictingjoins, that is joining tables that produce fewer rows first.