Se houver uma tabela com JOINs e precisar de OFFSET, como otimizar?

A utilização de OFFSET com JOINs muitas vezes leva a um desempenho ruim, pois a base de dados precisa processar e ordenar todas as linhas até ao deslocamento necessário.

Para otimizar, pode-se:

• Utilizar paginação por chave (paginaçã por conjunto de chaves), ou seja, em vez de OFFSET, usar uma condição WHERE com um filtro por um campo único ou ordenável (por exemplo, id > last_seen_id). Isto permite que a base utilize eficientemente os índices.

• Minimizar a quantidade de JOINs, se possível, obtendo previamente as chaves necessárias e depois recuperando os dados relacionados com consultas separadas.

• Adicionar os índices necessários nos campos de ordenação e filtragem.

Exemplo de paginação por conjunto de chaves:

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

Isto é muito mais eficiente do que OFFSET, especialmente em grandes volumes de dados.