MySQL Data - ¿La mejor manera de implementar paginación?

Para 500 registros la eficiencia probablemente no sea un problema, pero si tiene millones de registros, puede ser ventajoso usar una cláusula WHERE para seleccionar la siguiente página:

SELECT *
FROM yourtable
WHERE id > 234374
ORDER BY id
LIMIT 20

El "234374" aquí es el id del último registro de la página anterior que vio.

Esto permitirá que se use un índice en id para encontrar el primer registro. Si utilizas LIMIT offset, 20 podrías encontrar que se vuelve más y más lento a medida que navegas hacia el final. Como dije, probablemente no importará si solo tienes 200 registros, pero puede hacer una diferencia con conjuntos de resultados más grandes.

Otra ventaja de este enfoque es que si los datos cambian entre las llamadas, no perderá registros ni obtendrá un registro repetido. Esto se debe a que agregar o eliminar una fila significa que el desplazamiento de todas las filas después de que cambia. En su caso, probablemente no es importante, supongo que su grupo de anuncios no cambia demasiado a menudo y, de todos modos, nadie se daría cuenta si reciben el mismo anuncio dos veces seguidas, pero si está buscando la" mejor manera " entonces esto es otra cosa a tener en cuenta al elegir qué enfoque usar.

Si desea usar LIMIT con un offset (y esto es necesario si un usuario navega directamente a la página 10000 en lugar de paginar a través de páginas una por una), entonces puede leer este artículo sobre búsquedas finales de filas para mejorar el rendimiento de LIMIT con un offset grande.

Hay literatura al respecto:

• Se optimizó la paginación usando MySQL, haciendo la diferencia entre contar la cantidad total de filas y la paginación.

• Paginación Eficiente Usando MySQL, por Yahoo Inc. en la Percona Performance Conference 2009. El equipo de MySQL de Percona lo proporciona también como un video de Youtube: Paginación eficiente Usando MySQL (video),

El principal problema ocurre con el uso de grandes OFFSETs. Evitan usar OFFSET con una variedad de técnicas, que van desde las selecciones de rango id en la cláusula WHERE, hasta algún tipo de almacenamiento en caché o páginas pre-computacionales.

Hay soluciones sugeridas en Use el ÍNDICE, Lucas :

• "Paging Through Results ".

• "Paginación hecha de la manera correcta".

Define OFFSET para la consulta. Por ejemplo

Página 1 - (registros 01-10): offset = 0, limit = 10;

Página 2 - (registros 11-20) desplazamiento = 10, límite = 10;

Y utilice la siguiente consulta :

SELECT column FROM table LIMIT {someLimit} OFFSET {someOffset};

Ejemplo para la página 2:

SELECT column FROM table
LIMIT 10 OFFSET 10;

Este tutorial muestra una gran manera de hacer paginación. Paginación eficiente Usando MySQL

En resumen, evite usar DESPLAZAMIENTO o LÍMITE grande

También puedes hacer

SELECT SQL_CALC_FOUND_ROWS * FROM tbl limit 0, 20

El recuento de filas de la instrucción select (sin el límite) se captura en la misma instrucción select, de modo que no es necesario volver a consultar el tamaño de la tabla. Se obtiene el recuento de filas usando SELECT FOUND_ROWS ();

Consulta 1: SELECT * FROM yourtable WHERE id > 0 ORDER BY id LIMIT 500

Consulta 2: SELECT * FROM tbl LIMIT 0,500;

La consulta 1 se ejecuta más rápido con registros pequeños o medianos, si el número de registros es igual a 5,000 o superior, el resultado es similar.

Resultado para 500 registros:

Query1 take 9.9999904632568 milisegundos

Query2 take 19.999980926514 milisegundos

Resultado para 8.000 registros:

Query1 take 129.99987602234 milisegundos

Query2 toma 160.00008583069 milisegundos