CODEWITHSUNDEEP
mysqljoin
Omri Segal
Omri Segal

Reputation: 397

MySQL INNER JOIN vs "WHERE id IN ()"

I have a documents permissions table, and I wish to select a page of documents for a user. Thing is, a user can be permitted to a document through more than one group, thus we might have more than X records for X documents for the user.

I tried to use a subquery for paging, but MySQL didn't like LIMIT in the subquery.

I'm facing two major options:

  1. SELECT perm.* FROM
(SELECT DISTINCT document_id FROM permissions WHERE .... LIMIT...) ids
INNER JOIN
(SELECT fields FROM permissions WHERE ....) perms
ON ids.document_id = perms.document_id

  2. Using Java for the join - first read the document ids, and then use them in a query like:

    SELECT fields FROM permissions WHERE .... AND document_id IN([specific document_ids previously read])

What would be more efficient?

Upvotes: 0

Views: 3309

Answers (1)

Jorge_B
Jorge_B

Reputation: 9872

There are some different matters in your question. First of all:

1: I don't really understand what you do in your query, but the limit clause must be at the end of a query, so you could try 

select * from A join B on A.id = B.id limit 10

And this should work. More info on:

https://dev.mysql.com/doc/refman/5.0/en/select.html

2: Join vs. IN clause: the IN clause should always perform worse than the join. Imagine something like:

select * from A where A.id in (select id from B)

This will do a full scan on B table (select id from B subquery) and then another full scan on A to try match the results.

However, 

select * from A join B on A.id = B.id

should do a hash join between both tables, and if you have planned it right, id will be an index column, so it should be quite faster (and do no full scans on neither of them)

Upvotes: 1

Related Questions