在加入之前选择SELECT更好吗? [英] Is it better to SELECT before JOINING?

问题描述

我需要连接3个表a，b，c，并且我知道从最左边的表开始的一行仅必须出现在最终结果中.

I need to join 3 tables a,b,c and I know that only one row from the table most to the left has to appear in the end result.

SELECT * 
    FROM a 
        LEFT JOIN b 
            ON a.id = b.id 
        LEFT JOIN c 
            ON c.id2 = b.id2
    WHERE a.id = 12;

我提出了以下查询，因为它看起来效率更高，但是两个查询需要花费相同的时间来执行.这是因为第一个查询已优化吗?我应该麻烦选择效率更高的(第二个)查询，还是坚持第一个查询，因为它更具可读性?

I have come up with the following query because it seems more efficient, but both queries take the same time to execute. Is this because the first query is optimized? Should I bother to choose the more efficient (second) query or stick to the first one because it's more readable?

SELECT * 
    FROM (SELECT * FROM a WHERE id=12) AS temp 
        LEFT JOIN b 
            ON temp.id = b.id 
        LEFT JOIN c
            ON b.id2 = c.id2;

推荐答案

与优化查询一样，答案应该是:取决于. 答案取决于几件事，其中包括:

As always with optimizing queries, the answer should be: it depends. The answers depends on several things, among others:

• 使用查询2而不是查询1，实际上是否会对性能产生好处.这可以在为这些查询创建的查询计划中看到.两种查询创建的查询计划可以相同，但是使用索引时也可以不同.
• 这也可能取决于查询表中的行数.查询运行多长时间以及使用查询的频率.如果您开始优化每天使用一次并且运行几毫秒的查询，则最好使用可读性最好的查询.

因此，唯一能够真正确定您应该使用查询1还是查询2的人是:您.无法为您提供有关此主题的合理建议.

So the only person that can really determine whether you should use query 1 or query 2 is: You. It is impossible to give you sound advise on this topic.

这篇关于在加入之前选择SELECT更好吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！