Paginação do MySQL sem consulta dupla?

Eu queria saber se havia uma maneira de obter o número de resultados de uma consulta MySQL e, ao mesmo tempo, limitar os resultados.

A forma como a paginação funciona (pelo que entendi), primeiro eu faço algo como

query = SELECT COUNT(*) FROM `table` WHERE `some_condition` 

Depois que eu obtenho o num_rows (consulta), eu tenho o número de resultados. Mas para limitar meus resultados, tenho que fazer uma segunda consulta como:

 query2 = SELECT COUNT(*) FROM `table` WHERE `some_condition` LIMIT 0, 10 

Minha pergunta: Existe uma maneira de recuperar o número total de resultados que seriam dados E limitar os resultados retornados em uma única consulta? Ou qualquer maneira mais eficiente de fazer isso. Obrigado!

Não, é assim que muitos aplicativos que querem paginar precisam fazer isso. É confiável e à prova de balas, embora faça a consulta duas vezes. Mas você pode armazenar a contagem em cache por alguns segundos e isso ajudará muito.

A outra maneira é usar a cláusula SQL_CALC_FOUND_ROWS e, em seguida, chamar SELECT FOUND_ROWS() . Além do fato de que você tem que colocar a chamada FOUND_ROWS() depois, há um problema com isso: Existe um bug no MySQL que faz cócegas que afeta as consultas ORDER BY tornando muito mais lento em tabelas grandes do que a abordagem ingênua de duas consultas. .

Eu quase nunca faço duas consultas.

Basta retornar mais uma linha do que o necessário, exibir apenas 10 na página e, se houver mais do que exibido, exibir um botão “Avançar”.

 SELECT x, y, z FROM `table` WHERE `some_condition` LIMIT 0, 11 
 // iterate through and display 10 rows. // if there were 11 rows, display a "Next" button. 

Sua consulta deve retornar em uma ordem mais relevante primeiro. As chances são de que a maioria das pessoas não se importará em ir para a página 236 de 412.

Quando você faz uma pesquisa no google e seus resultados não estão na primeira página, você provavelmente vai para a página dois, não para nove.

Outra abordagem para evitar a consulta dupla é buscar todas as linhas da página atual usando uma cláusula LIMIT primeiro e, em seguida, fazer apenas uma segunda consulta COUNT (*) se o número máximo de linhas tiver sido recuperado.

Em muitas aplicações, o resultado mais provável será que todos os resultados se encaixem em uma página, e ter que fazer paginação é a exceção e não a norma. Nesses casos, a primeira consulta não recuperará o número máximo de resultados.

Por exemplo, as respostas em uma questão de stackoverflow raramente chegam a uma segunda página. Comentários sobre uma resposta raramente ultrapassam o limite de 5 ou mais, necessário para mostrar todos eles.

Portanto, nesses aplicativos, você pode simplesmente fazer uma consulta com um LIMIT primeiro e, enquanto esse limite não for alcançado, saberá exatamente quantas linhas existem sem a necessidade de fazer uma segunda consulta COUNT (*) – que deve cobrir a maioria das situações.

Na maioria das situações, é muito mais rápido e menos intensivo em resources fazer isso em duas consultas separadas do que em uma, embora isso pareça ser contra-intuitivo.

Se você usa SQL_CALC_FOUND_ROWS, então para tabelas grandes ele torna sua consulta muito mais lenta, significativamente mais lenta até do que executar duas consultas, a primeira com um COUNT (*) e a segunda com um LIMIT. A razão para isso é que SQL_CALC_FOUND_ROWS faz com que a cláusula LIMIT seja aplicada após buscar as linhas em vez de antes, portanto, busca a linha inteira para todos os resultados possíveis antes de aplicar os limites. Isso não pode ser satisfeito por um índice porque ele realmente busca os dados.

Se você adotar a abordagem de duas consultas, a primeira buscando apenas COUNT (*) e não buscando dados reais, isso pode ser satisfeito muito mais rapidamente, pois normalmente ele pode usar índices e não precisa buscar os dados reais da linha para cada linha que olha. Em seguida, a segunda consulta só precisa examinar as primeiras linhas $ offset + $ limite e, em seguida, retornar.

Esta postagem do blog de desempenho do MySQL explica isso ainda mais:

http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

Para mais informações sobre otimização de paginação, verifique esta postagem e esta postagem .

 query = SELECT col, col2, (SELECT COUNT(*) FROM `table`) AS total FROM `table` WHERE `some_condition` LIMIT 0, 10 

Minha resposta pode estar atrasada, mas você pode pular a segunda consulta (com o limite) e filtrar as informações através do seu script de back-end. No PHP, por exemplo, você poderia fazer algo como:

 if($queryResult > 0) { $counter = 0; foreach($queryResult AS $result) { if($counter >= $startAt AND $counter < $numOfRows) { //do what you want here } $counter++; } } 

Mas, claro, quando você tem milhares de registros para considerar, torna-se ineficiente muito rápido. Contagem pré-calculada talvez seja uma boa ideia investigar.

Aqui está uma boa leitura sobre o assunto: http://www.percona.com/ppc2009/PPC2009_mysql_pagination.pdf

Você pode reutilizar a maior parte da consulta em uma subconsulta e defini-la como um identificador. Por exemplo, uma consulta de filme que encontre filmes contendo a ordem da letra por tempo de execução ficaria assim em meu site.

 SELECT Movie.*, ( SELECT Count(1) FROM Movie INNER JOIN MovieGenre ON MovieGenre.MovieId = Movie.Id AND MovieGenre.GenreId = 11 WHERE Title LIKE '%s%' ) AS Count FROM Movie INNER JOIN MovieGenre ON MovieGenre.MovieId = Movie.Id AND MovieGenre.GenreId = 11 WHERE Title LIKE '%s%' LIMIT 8; 

Observe que não sou especialista em database e espero que alguém possa otimizar isso um pouco melhor. Como está sendo executado diretamente da interface de linha de comando SQL, ambos demoram ~ 0,02 segundo no meu laptop.

 SELECT * FROM table WHERE some_condition ORDER BY RAND() LIMIT 0, 10