Paginação MySQL: recuperando resultados e contagem total com eficiência

Ao implementar a paginação no MySQL, é prática comum realizar duas consultas separadas: uma para obter o número total de resultados e outro para limitar os registros buscados. Essa abordagem, conhecida como consulta dupla, pode ser ineficiente para grandes conjuntos de dados.

No entanto, surge a questão de saber se é possível recuperar a contagem total de resultados e limitar os resultados em uma única consulta. Embora não haja uma maneira direta de conseguir isso, dois métodos alternativos estão disponíveis:

SQL_CALC_FOUND_ROWS e FOUND_ROWS()

Em vez de consulta dupla, você pode utilizar o SQL_CALC_FOUND_ROWS e FOUND_ROWS() para obter um resultado semelhante. A consulta com SQL_CALC_FOUND_ROWS precede a consulta limitante, e a chamada FOUND_ROWS() busca a contagem total.

Exemplo:

SELECT SQL_CALC_FOUND_ROWS * FROM `table` WHERE `some_condition`;
SELECT FOUND_ROWS();

Nota: Este método tem uma desvantagem potencial. O MySQL tem um bug que afeta as consultas ORDER BY ao usar esta técnica, tornando-a mais lenta em tabelas grandes.

Armazenando a contagem em cache

Uma solução alternativa envolve armazenar em cache a contagem total por um período específico. Essa abordagem elimina a necessidade de uma consulta separada para buscar a contagem, preservando a eficiência.

Exemplo:

Recupere a contagem e armazene-a em cache por 10 minutos:

SET @count = (SELECT COUNT(*) FROM `table` WHERE `some_condition`);

Em consultas subsequentes, use o contagem em cache:

SELECT * FROM `table` WHERE `some_condition` LIMIT 0, 10;

Armazenar a contagem em cache melhora significativamente o desempenho, especialmente para páginas visitadas com frequência. Embora não seja uma solução de consulta única, ela alcança o mesmo resultado e é mais eficiente do que a consulta dupla.