Comunidad de diseño web y desarrollo en internet online

Optimizar consultas SQL para paginar resultados en MySQL

Con un título tan sexy y atrayente he captado tu atención en segundos. Empecemos por lo básico. Paginar es algo que todos utilizamos en una u otra forma. De hecho tu framework o lenguaje favorito debe incluir alguna etiqueta, módulo o plugin que te permite hacerlo. En el caso de Ruby on Rails, un plugin muy conocido es el "will_paginate".

Para que funcione, básicamente le decimos que pagine los resultados de algún modelo según la página que estamos revisando:

Código :

   @posts = Post.paginate :page => 10

En ese caso, buscará los resultados de la tabla "posts" correspondientes a la página 10. Si presentamos 10 resultados por página, eso sería el registro 101 al 110. El query que utiliza para ello es algo similar a:

Código :

select ALGO from posts limit 10 offset 100

Es decir, utilizando el offset y el limit obtiene los resultados específicos.

Ayer noté algo curioso sin embargo. En una base de datos que supera los 300,000 registros, saltar a la página 8000 era horrendamente lento. Luego de descartar algún bug en el plugin y el mismo framework, salté a la BD para probar:

Código :

select * from posts limit 100 offset 100

Resultado: 120ms

Código :

select * from posts limit 100 offset 1000

Resultado: 135ms

Código :

select * from posts limit 100 offset 100000

Resultado: 140ms

Hasta ahí todo iba bien. Probé entonces forzando el offset a mayores niveles:

Código :

select * from posts limit 100 offset 200000

Resultado: 40 segundos

40. segundos. 40. Agregar 100,000 filas al offset hacía que el query creciera tanto como la deuda externa de un país.

Recurrí a las internets para investigar algo y encontré esta interesante presentación de Yahoo!: Pagination using mysql

En términos simples, proponen no usar OFFSET (que al parecer requiere recorrer mucho los data sets y levantar registros a memoria) y basarnos en un filtrado más rápido.

El nuevo enfoque buscaría que hagamos lo siguiente: en lugar de describir rangos de resultados (del 101 al 100), pedirle a la BD que brinde los registros cuyo identificador sea mayor a un valor. En este caso, mayor a 100, y que brinde de ellos 10 nada más:

Código :

select * from posts where id > 100 limit 10


LIMIT sólo es un buen chico y sin el offset funciona bien.

Probando este enfoque, repetí el código en la BD:

Código :

select * from posts where id > 200000 limit 100

Resultado: 135 ms

Genial. De esta manera, el nuevo sistema de paginación funciona bien si mantenemos el último ID o identificador del último rango y lo solicitamos para la siguiente página que queremos visualizar. Funciona rápido y bien.

Conclusiones


Si tienes en tu BD registros de menos de 150,000 filas, es probable que no pase nada si usas el offset (que te hecho es bastante sencillo de utilizar). Si los sobrepasas, evaluar nuevas opciones de queries permitirán conocer más tu SGDB en particular y buscar mejores soluciones

Disclaimer


No soy DBA ni pretendo serlo. Este tipo nace de una evaluación empírica y de un proyecto en producción que, al menos por ahora, funciona bastante bien con los cambios realizados. Si por ahí hay un mago de las matemáticas booleanas y relacionales que quiera matarme con un bate, bienvenido está en los comentarios. Además así todos aprendemos. Bastardos.

¿Sabes SQL? ¿No-SQL? Aprende MySQL, PostgreSQL, MongoDB, Redis y más con el Curso Profesional de Bases de Datos que empieza el martes, en vivo.

Publica tu comentario

o puedes...

¿Estás registrado en Cristalab y quieres
publicar tu URL y avatar?

¿No estás registrado aún pero quieres hacerlo antes de publicar tu comentario?

Registrate