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.
Si se borran los registros no pasa nada. Si tienes los registros 1,2,400,450,2000 y 2010, y muestras dos registros por página, la primera página sería (si pensamos en sólo el query:
Código :
La premisa es filtrar por el último Id del rango anterior. En el caso inicial, sería 0. Ahora hemos recibido dos elementos, [1,2]. Entonces, la siguiente página será todos a partir del último ID:
Código :
Ahora obtendremos de resultados [400,450]. La tercera página sería:
Código :
Y obtendremos los resultados 2000 y 2010, etc., etc.
Si no usas un Identificador numérico, bien podría ser el timestamp de tu registro (created_at, updated_at, o lo que uses) y filtrar según el UNIX timestamp
Por otro lado, para una tabla con tantos registros se debería ofrecer paginacion con filtros. Y no asi una paginación de tooodos los registros sin filtro
saludos
Código :
Y así lista los primeros 30 registros ordenados por id de manera ASC, y si deseo la siguiente páginaCódigo :
Si observas LIMIT registro_inicial, numero_registros Para calcular el registro inicial con el número de la página podrías utilizar algo como
Código :
tampoco presentaría ningún problema si el id no es numéricoMaikel :
BTW, cuando ordenas, no es necesario añadir el "ASC", es el modo por default
Maikel: En este caso era para una zona de administración, donde descubrimos que la paginación normal nos daba una mala pasada. De hecho para la mayoría de usos emplearemos el buscador y no ir de página en página, pero me pareció curioso como escalaba la lentitud
Por Inyaka el 25 de Noviembre de 2010
por cierto, sobre todo a las tablas grandes siempre les coloco un campo llamado id como PK el cual es autoincremental, quizás a las tablas relacionales podamos obviar esto pero no a una tabla con datos.
Por Tufik el 25 de Noviembre de 2010
El problema se presenta al hacer la consulta con rangos altos, o al tener una tabla con una cantidad de registros muy alta?
NeoCesar :
buen tip compa!
De hecho hacer consultas en muchos registros no es malo ni lento. De hecho, media Internet funciona así. En este caso es una forma que tiene MySQL de ejecutar los queries via el LIMIT y OFFSET (o con el segundo parámetro del LIMIT) que hace que buscar otras opciones sea mejor.
Por Rodrigo Gomez el 26 de Noviembre de 2010
http://www.youtube.com/watch?v=INWdgEQMTd0
Por tufik el 27 de Noviembre de 2010
Por baccxus el 27 de Noviembre de 2010
ejemplo hecho a mano (por favor no intentar en casa sin la compañía de un adulto responsable)
$idstart=($pagina * 100)+1;
Saludos
saludos
Por min el 28 de Noviembre de 2010
Por Tufik el 29 de Noviembre de 2010
Normalmente son 1,2,5,10,11,12,16. Oooo si lográramos que el id sea 1,2,3,4,5 generalmente en nuestra tabla hay presente un campo para manejar su estado (Activo e Inactivo), los Ids activos nunca van a ser como tu dices.
El ejemplo que tu das casi nunca se va a presentar en la vida real.
Muy buen método, pero seria perfecto si se pudiente saltar de la pagina 1 a la 10.
por ejemplo si tienes una tabla
ID ---> DATO1 ----> DATO2 ----> FECHA1
puedes hacer una búsqueda del tipo
SELECT * FROM `tabla` LIMIT `$idstart`,`$idend`
si te fijas el limitante no depende del ID que tenga la tabla, si no del que genera la búsqueda.
A lo que voy, es que el ID es irrelevante en la generación de resultados, precisamente por eso que dices "ya que es muy difícil mantener un id autonumerico".
Saludos
Quise decir que la búsqueda autonumera el resulado
Por Tufik el 30 de Noviembre de 2010
El problema o la pregunta que expuse va relacionado con este post y con la solución que se plantea:
select * from posts where id > 100 limit 10
no enfocado a utilizar los dos parámetros de LIMIT n,m
teniendo en cuenta que:
Código :
Si hago esta búsqueda
Código :
me dá lo mismo que:
Código :
Saludos
@Tufik, en el link que aporta @Yaraher se plantea que se cambió la interfaz de usuario, ya no se saltará a la página N, sino que solamente se podrá ir al primer resultado, al siguiente resultado y al anterior resultado.
Saludos
Por Gustavo el 07 de Diciembre de 2010
select * from posts where id > 100 limit 10
devuelve la pagina 10, pero que pasa si se borran 20 registros antes del id 100??
Los resultados que me devuelve la consulta estarian errados.
elchininet-blog :
Con esta solución no se puede mostrar directamente la página 10, te muestra la 1 y vas avanzando o retrocediendo en las páginas de 1 en 1, sólo puedes saltar al inicio desde cualquier página.
Por Seba el 03 de Octubre de 2012
Pulgar abajo
Por Boris el 07 de Noviembre de 2012
select * from posts limit 100 offset 200000
Resultado = 40 ms
select * from posts where id > 200000 limit 100
Resultado=135 ms
las dos se suponen que vendrían a ser análogas según lo que has dicho y sin embargo la que tiene offset se demora menor :/, igual gracias por intentar ayudar a optimizar la consulta, me quedare con el offset para paginar
Por EnaNoO el 12 de Septiembre de 2013
Por ruben el 24 de Diciembre de 2014
select * from posts where 1 limit 200000,100 , creo que es mas rapido
Por Jose el 14 de Noviembre de 2018