So the problem is that you have two tables say A and B and you need to find the records that exist in table B which don't exist in table A. A very common requirement while you work with the database. There are multiple ways of doing this.
1. Use a left join query
2. Use a not in query
3. Use a not exists query
The link http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
explains each method in more detail. I've always struggled with which way is faster. Sometimes I go with whichever I find convenient. But looks like the link says the left join approach is the fastest.
Regardless, one has to make sure to use "explain" with your query to check if you are using indexes in your query plan. Queries that don't use indexes are slow running especially if you have tons of rows to join.
1. Use a left join query
2. Use a not in query
3. Use a not exists query
The link http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
explains each method in more detail. I've always struggled with which way is faster. Sometimes I go with whichever I find convenient. But looks like the link says the left join approach is the fastest.
Regardless, one has to make sure to use "explain" with your query to check if you are using indexes in your query plan. Queries that don't use indexes are slow running especially if you have tons of rows to join.
No comments:
Post a Comment