If there is a table with JOINs and an OFFSET is needed — how to optimize?

Using OFFSET with JOINs often leads to poor performance because the database has to process and sort all rows up to the desired offset.

To optimize:

• Use keyset pagination, i.e., instead of OFFSET, use a WHERE condition with a filter on a unique or sortable field (e.g., id > last_seen_id). This allows the database to efficiently utilize indexes.

• Minimize the number of JOINs by pre-fetching necessary keys and then retrieving related data with separate queries.

• Add necessary indexes on sorting and filtering fields.

Example of keyset pagination:

SELECT t1.*, t2.* FROM table1 t1
JOIN table2 t2 ON t1.ref_id = t2.id
WHERE t1.id > ?
ORDER BY t1.id
LIMIT 20;

This is much more efficient than OFFSET, especially on large datasets.