Is it better to use INNER JOIN or EXISTS to find belonging to several in m2m relation?
OPTION A
JOIN
has an advantage over EXIST
, because it will more efficiently use the indices, especially in case of large tables
A JOIN
is more efficient, generally speaking.
However, one thing to be aware of is that joins can produce duplicate rows in your output. For example, if item id was in category 1 and 3, the first JOIN
would result in two rows for id 123. If item id 999 was in categories 1,3,7,8,12, and 66, you would get eight rows for 999 in your results (2*2*2).
Duplicate rows are something you need to be aware of and handle. In this case, you could just use select distinct id...
. Eliminating duplicates can get more complicated with a complex query, though.
select distinct `user_posts_id` from `user_posts_boxes` where `user_id` = 5 and exists (select * from `box` where `user_posts_boxes`.`box_id` = `box`.`id` and `status` in ("A","F")) order by `user_posts_id` desc limit 200; select distinct `user_posts_id` from `user_posts_boxes` INNER JOIN box on box.id = `user_posts_boxes`.`box_id` and box.`status` in ("A","F") and box.user_id = 5 order by `user_posts_id` desc limit 200
I tried with both query, But above query works faster for me.Both tables having large dataset. Almost "user_posts_boxes" has 4 million and boxes are 1.5 million.
First query took = 0.147 ms2nd Query almost = 0.5 to 0.9 MS
But my database tables are inno db and having physical relationships are also applied.
SO I should go for exists but it also depends upon how you have your db structure.