Paginación MySQL sin doble consulta?

pagination double

Question

Me preguntaba si había una manera de obtener la cantidad de resultados de una consulta MySQL y, al mismo tiempo, limitar los resultados.

La forma en que funciona la paginación (según entiendo), primero hago algo así como

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

Después de obtener el num_rows (consulta), tengo la cantidad de resultados. Pero para limitar mis resultados, tengo que hacer una segunda consulta como:

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

Mi pregunta: ¿hay alguna forma de recuperar el número total de resultados que se darían, Y limitar los resultados devueltos en una sola consulta? O cualquier forma más eficiente de hacer esto. ¡Gracias!

Answer 1

Casi nunca hago dos consultas.

Simplemente devuelva una fila más de la necesaria, solo muestre 10 en la página, y si hay más de lo que se muestra, muestre un botón "Siguiente".

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.

Su consulta debe regresar en un orden de lo más relevante primero. Lo más probable es que a la mayoría de las personas no les importe ir a la página 236 de 412.

Cuando hace una búsqueda en Google, y sus resultados no están en la primera página, es probable que vaya a la página dos, no a la nueve.

Answer 2

En la mayoría de las situaciones, es mucho más rápido y consume menos recursos hacerlo en dos consultas separadas que hacerlo en una, aunque eso parezca contrario a la intuición.

Si usa SQL_CALC_FOUND_ROWS, para las tablas grandes hace que su consulta sea mucho más lenta, significativamente más lenta incluso que la ejecución de dos consultas, la primera con un COUNT (*) y la segunda con un LIMIT. La razón de esto es que SQL_CALC_FOUND_ROWS hace que la cláusula LIMIT se aplique después de buscar las filas en lugar de antes, por lo que recupera toda la fila para obtener todos los resultados posibles antes de aplicar los límites. Esto no puede ser satisfecho por un índice porque realmente obtiene los datos.

Si toma el enfoque de las dos consultas, el primero solo busca COUNT (*) y no busca realmente datos reales, esto puede cumplirse mucho más rápido porque generalmente puede usar índices y no tiene que buscar los datos reales de la fila para cada fila que mira. Entonces, la segunda consulta solo necesita mirar las primeras filas $ offset + $ limit y luego regresar.

Esta publicación del blog de rendimiento de MySQL explica esto más a fondo:

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

Para obtener más información sobre cómo optimizar la paginación, consulte esta publicación y esta publicación .

Answer 3

Mi respuesta puede ser tardía, pero puede omitir la segunda consulta (con el límite) y simplemente filtrar la información a través de su script de back-end. En PHP, por ejemplo, podrías hacer algo como:

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

Pero, por supuesto, cuando tiene miles de registros para considerar, se vuelve ineficiente muy rápido. El recuento precalculado tal vez sea una buena idea para investigar.

Aquí hay una buena lectura sobre el tema: http://www.percona.com/ppc2009/PPC2009_mysql_pagination.pdf

Answer 4

No, así es como muchas aplicaciones que quieren paginar tienen que hacerlo. Es confiable ya prueba de balas, aunque hace la consulta dos veces. Pero puedes guardar el conteo por unos segundos y eso te ayudará mucho.

La otra forma es usar la cláusula SQL_CALC_FOUND_ROWS y luego llamar a SELECT FOUND_ROWS() . aparte del hecho de que tiene que poner la llamada FOUND_ROWS() después, hay un problema con esto: Hay un error en MySQL que hace cosquillas que afecta las consultas ORDER BY lo que hace que sea mucho más lento en tablas grandes que el enfoque ingenuo de dos consultas .

Answer 5

Otro método para evitar la doble consulta es buscar primero todas las filas de la página actual usando primero una cláusula LIMIT, luego solo hacer una segunda consulta COUNT (*) si se recuperó el número máximo de filas.

En muchas aplicaciones, el resultado más probable será que todos los resultados quepan en una página, y tener que hacer la paginación es la excepción más que la norma. En estos casos, la primera consulta no recuperará la cantidad máxima de resultados.

Por ejemplo, las respuestas en una pregunta de raramente se vierten en una segunda página. Los comentarios sobre una respuesta rara vez superan el límite de 5 o más requerido para mostrarlos todos.

Entonces, en estas aplicaciones simplemente puede hacer una consulta con un LÍMITE primero, y luego, mientras no se alcance ese límite, sabrá exactamente cuántas filas hay sin la necesidad de hacer una segunda consulta COUNT (*), que debería cubrir la mayoría de las situaciones.

Answer 6

Puede reutilizar la mayor parte de la consulta en una subconsulta y establecerla en un identificador. Por ejemplo, una consulta de película que encuentre películas que contengan el orden de las letras por tiempo de ejecución se vería así en mi sitio.

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;

Tenga en cuenta que no soy un experto en bases de datos, y espero que alguien pueda optimizarlo un poco mejor. Tal como está funcionando directamente desde la interfaz de línea de comandos SQL, ambos toman ~ 0.02 segundos en mi computadora portátil.

Answer 7

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

Answer 8

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