Cómo paginar miles de registros en MySQL sin que las últimas páginas se vuelvan lentas

Las primeras páginas del historial de pedidos cargan rápido, pero el listado tarda cada vez más al avanzar. Si la consulta usa LIMIT con un OFFSET grande, MySQL tiene que recorrer y descartar los resultados previos antes de devolver los que vas a mostrar. La paginación por cursor usa los valores del último pedido visible para indicarle desde dónde continuar.

Por qué se vuelve lento un OFFSET alto

Con 20 pedidos por página, la página 2501 empieza después de 50 000 resultados:

SELECT id, fecha_creacion, total
FROM pedidos
WHERE cliente_id = 42
ORDER BY fecha_creacion DESC, id DESC
LIMIT 20 OFFSET 50000;

Aunque un índice permita leer los pedidos en el orden solicitado, el desplazamiento sigue obligando a saltar esos 50 000 resultados. Si el plan necesita ordenar por separado, puede haber trabajo adicional. El costo de una página depende del plan y del volumen de datos.

1. Define un orden estable y crea el índice

En este ejemplo, pedidos es una tabla InnoDB en MySQL 8, id es una clave primaria y fecha_creacion es un DATETIME no nulo. Ordenamos de más reciente a más antiguo y usamos id para resolver empates entre pedidos de la misma fecha.

CREATE INDEX idx_pedidos_cliente_fecha_id
ON pedidos (cliente_id, fecha_creacion DESC, id DESC);

El índice empieza por el cliente, que es el filtro de igualdad. Después vienen la fecha y el identificador, en el mismo orden que usa el listado. Revisa primero SHOW INDEX FROM pedidos; por si ya tienes un índice equivalente.

2. Obtén la primera página y guarda el cursor

SELECT id, fecha_creacion, total
FROM pedidos
WHERE cliente_id = 42
ORDER BY fecha_creacion DESC, id DESC
LIMIT 21;

Solicita 21 filas y muestra hasta 20. Si llega la fila extra, hay una página siguiente en el momento de la consulta. Construye el cursor con la fecha y el ID de la fila 20, la última que sí mostraste. Si usas la fila 21 como cursor, saltarás ese pedido.

3. Usa el cursor para pedir la página siguiente

SELECT id, fecha_creacion, total
FROM pedidos
WHERE cliente_id = :cliente_id
  AND (
    fecha_creacion < :fecha_limite
    OR (fecha_creacion = :fecha_empate AND id < :id_limite)
  )
ORDER BY fecha_creacion DESC, id DESC
LIMIT 21;

Si el último pedido visible fue el 98123 con fecha 2026-11-12 09:15:00, enlaza esa fecha a los dos parámetros de fecha y el ID a :id_limite. La consulta incluye los pedidos de fechas anteriores y, en caso de empate, solo los de ID menor. En PHP, enlaza los valores con una consulta preparada de PDO. Los nombres de los parámetros de fecha son distintos para que funcionen también con preparaciones nativas.

Conserva la precisión original de la fecha, incluidas las fracciones de segundo si existen. Obtén el cliente autorizado desde la sesión o desde los permisos del usuario. El cursor solo indica una posición. Si cambia el cliente, el filtro o el orden, reinicia desde la primera página.

4. Mide el cambio con EXPLAIN ANALYZE

En MySQL 8.0.18 o posterior, ejecuta en pruebas las dos consultas con el prefijo EXPLAIN ANALYZE. Este comando ejecuta el SELECT y muestra métricas reales por operación. Para comparar la misma porción del historial, usa como cursor la fila 50 000 del mismo listado, con datos que se mantengan estables durante la prueba. Usa LIMIT 20 en ambas consultas para igualar el tamaño de la respuesta.

• Comprueba si MySQL eligió el índice compuesto y si la consulta por cursor usa un acceso por rango.
• Revisa las filas producidas por los pasos de acceso y filtrado, además de los tiempos reales.
• Prueba con clientes de distintos tamaños. Si el plan sigue leyendo muchas filas, verifica que los filtros, el orden y el índice coincidan con los de la consulta real.

Cuándo conviene cada método

El cursor encaja en historiales con «Siguiente», «Cargar más» o desplazamiento continuo. Si también necesitas «Anterior», toma la primera fila visible como límite, invierte las comparaciones y el orden SQL, y vuelve a invertir las filas que vas a mostrar. OFFSET sigue siendo práctico para listados pequeños que necesitan saltar directamente a una página numerada.

El cursor mantiene la posición ante nuevas filas al inicio del listado, pero las páginas siguen siendo consultas independientes. Si cambia la fecha de un pedido entre solicitudes, ese pedido puede moverse y aparecer dos veces o quedar fuera del recorrido. Usa columnas de ordenación estables y diseña una captura consistente de los datos cuando el caso, como una exportación, la requiera.

Referencias y lectura relacionada

• Cómo acelerar consultas lentas en MySQL con índices y EXPLAIN
• Manual de MySQL sobre la optimización de LIMIT
• Manual de MySQL sobre la optimización de ORDER BY