An optimum way to locate the right OFFSET for a given data

user_72437 0
Viewed 158

To avoid fetching all data at once, which will cause out of memory, we are implementing paging in our app, by using limit & offset.

Each time, our app will only display 1 page.

Page 0 : select * from note order by order_id limit 10000 offset 0;
Page 1 : select * from note order by order_id limit 10000 offset 10000;
Page 2 : select * from note order by order_id limit 10000 offset 20000;
Page 3 : select * from note order by order_id limit 10000 offset 30000;
...

Whenever user adds a new data, we know the search criteria to locate data from SQLite.

select * from note where uuid = '1234-5678-9ABC';

However, we need to reload our app with the correct page.

But, we have no idea, how to have a good speed performance, to find out which page (which offset), the new data belongs to.

We can have the following brute force way to find out which offset the data belongs to

select * from (select * from note order by order_id limit 10000 offset 0) where uuid = '1234-5678-9ABC';

select * from (select * from note order by order_id limit 10000 offset 10000) where uuid = '1234-5678-9ABC';

select * from (select * from note order by order_id limit 10000 offset 20000) where uuid = '1234-5678-9ABC';

select * from (select * from note order by order_id limit 10000 offset 30000) where uuid = '1234-5678-9ABC';

...

But, that is highly inefficient.

Is there any "smart" way, so that we can have a good speed performance, to locate the right offset for a given data?

Thanks.

sqlite
1 Answers

Use row_number() to find offset

The offset can be calculated by using the row_number window function to compute a row index. https://www.sqlite.org/windowfunctions.html#builtins

FIND ROW INDEX

select
    uuid, (row_number() over (order by order_id) - 1) as row_index
from note

FIND OFFSET

The offset can be computed using modular arithmetic.

select row_index - (row_index % 10000) as offset
from note_row_index
where uuid = '1234-5678-9ABC'

USE OFFSET IN QUERY

with note_row_index(uuid, row_index) AS (
    select
        uuid, (row_number() over (order by order_id) - 1) as row_index
    from note
),
note_offset(offset) AS (
    select row_index - (row_index % 10000) as offset
    from note_row_index
    where uuid = '1234-5678-9ABC'
)
select *
from note
order by order_id
limit 10000
offset (select offset from note_offset)
user_3099600
Related