0

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?

Omri Segal
  • 377
  • 4
  • 15
  • You want to find this [answer](http://stackoverflow.com/questions/5274618/inner-join-and-where-in-clause-performance) may help you. – Sadikhasan Mar 03 '14 at 10:42
  • MySQL didn't like subqueries and didn't like you put a limit into it. The limit itself is a database specific so it's not a standard. You can use setMaxResults as an alternative. – Roman C Mar 03 '14 at 10:42
  • Revise your query, it can be considerably simpler. Are you sure that you need to query the SAME table twice? You should be able to apply all `WHERE` criteria in one `SELECT` – Germann Arlington Mar 03 '14 at 10:43

1 Answers1

1

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)

Jorge_B
  • 9,712
  • 2
  • 17
  • 22
  • RE 1: Thing is, I want more than 10 rows, I want all relevant rows for the first 10 document ids. It's basically the same conditions in both queries. Since it's a very big table, I'd wish to avoid a hash join. RE 2: The first query you wrote is not exactly what I meant, as I wish to call the "subquery" only once, from Java, and then put the specific IDs in the IN clause. How would that be? – Omri Segal Mar 03 '14 at 11:30