StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIs it better to use INNER JOIN or EXISTS to find belonging to several in m2m relation?
text
Body
copied!<p>Given m2m relation: <strong>items-categories</strong> I have three tables: </p> <ul> <li><strong>items</strong>, </li> <li><strong>categories</strong> and </li> <li><strong>items_categories</strong> that hold references to both</li> </ul> <p>I want to find an item belonging to <strong>all given</strong> category sets:</p> <pre><code>Find Item belonging to a category in [1,3,6] and belonging to a category in [7,8,4] and belonging to a category in [12,66,42] and ... </code></pre> <p>There are two ways I can think of to accomplish this in mySQL.</p> <p><strong>OPTION A: INNER JOIN:</strong></p> <pre><code>SELECT id from items INNER JOIN category c1 ON (item.id = c1.item_id) INNER JOIN category c2 ON (item.id = c2.item_id) INNER JOIN category c3 ON (item.id = c3.item_id) ... WHERE c1.category_id IN [1,3,6] AND c2.category_id IN [7,8,4] AND c3.category_id IN [12,66,42] AND ...; </code></pre> <p><strong>OPTION B: EXISTS:</strong></p> <pre><code>SELECT id from items WHERE EXISTS(SELECT category_id FROM category WHERE category.item_id = id AND category_id in [1,3,6] AND EXISTS(SELECT category_id FROM category WHERE category.item_id = id AND category_id in [7,8,4] AND EXISTS(SELECT category_id FROM category WHERE category.item_id = id AND category_id in [12,66,42] AND ...; </code></pre> <p>Both options work. The question is: <strong>Which is the fastest / most optimal for large item table?</strong> Or is there an OPTION C I am missing?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload