For mysql, subqueries and joins are never recommended. Because the efficiency of join itself is flawed, it is difficult to guarantee the efficiency once the amount of data increases. It is strongly recommended to fetch data from a single table based on the index, and then join and merge the data in the program.
as described on stackoverflow.http://stackoverflow.com/questions/38...
7 down vote accepted
I would EXPECT the first query to be quicker, mainly because you have an equivalence and an explicit JOIN. In my experience IN is a very slow operator, since SQL normally evaluates it as a series of WHERE clauses separated by "OR" (WHERE x=Y OR x=Z OR...).
As with ALL THINGS SQL though, your mileage may vary. The speed will depend a lot on indexes (do you have indexes on both ID columns? That will help a lot...) among other things.
The only REAL way to tell with 100% certainty which is faster is to turn on performance tracking (IO Statistics is especially useful) and run them both. Make sure to clear your cache between runs!
I have not carefully understood the difference, but for situations where the data is relatively small (little pressure), both are available, and the performance is estimated to be not much different; for situations where the amount of data is large, both JOIN and subquery It is unacceptable and requires additional optimization (such as using NoSQL for caching).
Don’t use subqueries, the efficiency is too poor. If it is JOIN, it will be a nested query. Small tables drive large tables and are related through index fields. If the table records are relatively small, it is still OK. If it is large, the processing can be controlled in the business logic.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
For mysql, subqueries and joins are never recommended. Because the efficiency of join itself is flawed, it is difficult to guarantee the efficiency once the amount of data increases. It is strongly recommended to fetch data from a single table based on the index, and then join and merge the data in the program.
as described on stackoverflow.http://stackoverflow.com/questions/38...
7 down vote accepted
I would EXPECT the first query to be quicker, mainly because you have an equivalence and an explicit JOIN. In my experience IN is a very slow operator, since SQL normally evaluates it as a series of WHERE clauses separated by "OR" (WHERE x=Y OR x=Z OR...).
As with ALL THINGS SQL though, your mileage may vary. The speed will depend a lot on indexes (do you have indexes on both ID columns? That will help a lot...) among other things.
The only REAL way to tell with 100% certainty which is faster is to turn on performance tracking (IO Statistics is especially useful) and run them both. Make sure to clear your cache between runs!
I have not carefully understood the difference, but for situations where the data is relatively small (little pressure), both are available, and the performance is estimated to be not much different; for situations where the amount of data is large, both JOIN and subquery It is unacceptable and requires additional optimization (such as using NoSQL for caching).
I think it doesn’t matter if the amount of data is small, but this method won’t be used if the amount of data is large.
It doesn’t matter if the amount of data is small, but try to avoid it if the amount of data is large.
http://chaous.com/MySQL/2012/03/27/my...
I wrote an article before about the MySQL IN operation, I hope it will be useful to LZ.
As for how to understand JOIN query, I recommend a book by LZ http://book.douban.com/subject/105696...
I don’t know the specific performance. But I read that others said that join is used instead of subquery.
Don’t use subqueries, the efficiency is too poor. If it is JOIN, it will be a nested query. Small tables drive large tables and are related through index fields. If the table records are relatively small, it is still OK. If it is large, the processing can be controlled in the business logic.
Usually use join
If you are doing analysis, use join