Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

mysql - Which one is faster: correlated subqueries or join?

I know we can do correlated subqueries and join. But which one is faster? Is there a golden rule or I must measure both?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

First, a correlated subquery really is a type of join. There is no golden rule about which produces the best execution plan. If you are interested in performance, you need to try out the different forms to see what works best. Or, at least, look at the exeuction plans to make that decision.

In general, I tend to avoid correlated subqueries for a couple of reasons. First, they can almost always be written without the correlation. Second, many query engines turn them into nested loop joins (albeit using indexes), and other join strategies might be better. In such cases, correlated subqueries make it difficult to parallelize the query. Third, correlated subqueries are usualy in either the SELECT or WHERE clauses. I like for all my tables to be in the FROM clause.

In MySQL however, correlated subqueries are often the most efficient way to do a query. This is especially true when using a subquery in an IN clause. So, there is no golden rule.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...