如何优化MySQL包含表和sql视图左连接的查询
我有这个查询：
SELECT a.id, b.discount FROM (a LEFT JOIN b ON b.id_a = a.id);
此查询的 EXPLAIN 命令是这样的：
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a index NULL PRIMARY 4 NULL 1489 Using index
1 SIMPLE b ALL NULL NULL NULL NULL 819
A 是包含 1489 行的表，B 是 SQL 视图。现在我明白了，MySQL 必须执行 1489*819 次操作，这是未优化的操作。如果 B 是一个表，我会为 id_a 列创建一个索引，但我不知道如何处理 SQL 视图。
有人可以帮忙吗？
I have this query:
SELECT a.id, b.discount FROM (a LEFT JOIN b ON b.id_a = a.id);
EXPLAIN command for this query is this:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a index NULL PRIMARY 4 NULL 1489 Using index
1 SIMPLE b ALL NULL NULL NULL NULL 819
A is table with 1489 rows and B is a SQL view. Now I understand, that MySQL has to do 1489*819 operations which is way too not-optimized operation. If B was a table, I would create an index for column id_a, but I have no idea what to do with SQL view.
Anybody can help ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我会查看该视图，并尝试转到限定您关心的元素是否存在的原始表。并确保“b”视图的根表在该键上有索引。
I would look at the view, and try to go to the raw tables that qualify the element you are concerned with finding exists or not. And ensure the root table of "b" view has an index on that key.
您还可以尝试创建带有索引的临时表，而不是使用视图（未索引）。我们对一些冗长的报告查询执行了此操作，并因此获得了一些令人印象深刻的速度改进（当然是 YMMV）。
Instead of working with a view (which is unindexed), you could also try creating a temporary table with indexes. We did that for some lengthy reporting queries and got some impressive speed improvements as a result (YMMV of course).
切换表和视图可能会有所帮助。
例子：
Switching the table and view might help.
example: