Optimizing query with limit/offset?

1

i'm using sqlite, i have such query

select view1.a, view1.b, tbl1.c. tbl1.d
from ...
join ... on ...
join ... on ...
join ... on ...
where view1.a > 0 and view1.b > 0
order by view1.a, view1.b

union

select view2.a, view2.b, tbl2.c. tbl2.d
from ...
join ... on ...
join ... on ...
join ... on ...
where view2.a > 0 and view2.b > 0
order by view2.a, view2.b

it works but is slow for when returned row count is 100000+

so i thought to apply limit e.g. 200

but it still took the same time as before

i read that with limit/offset query is still executed, results are fetched, offset amount of rows are discarded, after which limit amount of rows are returned

and that i should utilize where clause instead, remember last id which is fetched and perform next fetch starting from that id

but the problem is that my view.a and view.b don't always start at 0/1 and i don't know how to get the last one

2

hi

please see if this link helps you !! or at least gives you ideas

3

@harishgg1 thanks for reply.
actually there is a composite index on view.a and view.b (in the table from which view is constructed)