However, for large view1 tables (1 mio entries), the
limit in the nested query is much slower than the
range of rowids.
This is slow, because the entire view2 table
is iterated:
SELECT *
FROM data
WHERE oid IN
(SELECT dataid
FROM view1
WHERE LIMIT 50 OFFSET 999949)
ORDER BY number; -- this sorting is needed, to
-- get the result in order!
This is fast, because the rowid is the primary
key of the viewid table and access is therefore
fast:
SELECT *
FROM data
WHERE oid IN
(SELECT dataid
FROM view1
WHERE rowid between 999950 AND 1000000)
ORDER BY number; -- this sorting is needed, to
-- get the result in order!
Wouldn't LIMIT and OFFSET do the trick for you?
Good point! I have not considered LIMIT and OFFSET.
However applying it directly on the original query
is very expensive, because it evaluates the query
on each call:
SELECT *
FROM data
WHERE name like 'foo%' ORDER number
LIMIT 50 OFFSET 99; -- retrieve column 100-150
I could use it on the query of my view1 table:
SELECT *
FROM data
WHERE oid IN
(SELECT dataid
FROM view1
WHERE LIMIT 50 OFFSET 99)
ORDER BY number; -- this sorting is needed, to
-- get the result in order!
But I'm still assuming that the view1 table is in the
order it was created (but no assumptions on the rowid
are needed!)...
Michael
