Después del tsunami menéame de ayer (en un par de horas tuve el tráfico de casi un mes) vamos a retomar un poco la temática habitual hablando de varios trucos para paginar datos en MySQL.
¿Qué es el paginado?
El paginado de datos consiste en trocear una salida de datos muy larga en paquetes mas pequeños y digeribles para el usuario. Hay ejemplos de paginados en la inmensa mayoría de aplicaciones, especialmente en las de Internet. Por ejemplo:
Paginado de Google:
Paginado de Flickr:
Paginado de Digg:
¿Por qué debo paginar?
- Para el usuario es más sencillo ver 10 paquetes de 10 resultados que 100 de golpe.
- Probablemente al usuario sólo le interesen los resultados más relevantes que son los que aparecen en la primera o segunda página.
- Para la base de datos es más sencillo y rápido traer 10 resultados que 100.
- Para el servidor HTTP es más sencillo y rápido servir 10 resultados que pesan 20Kb en lugar de 100 resultados que pesan 200Kb.
¿Cómo paginar datos?
Para paginar los datos necesitamos de antemano dos cosas: el tamaño de la pagina y el total de filas que tenemos de datos.
Contamos el total de filas:
-
-
SELECT COUNT(*) AS total FROM employees;
total
---------------
63
Mediante la instrucción SQL LIMIT n,m podemos trocear los datos de salida y mostrar la pagina que nos interese. LIMIT acepta dos parámetros: el primero es el offset de la pagina y el segundo la cantidad de filas que devuelve.
Devolvemos 10 resultados de la pagina 3:
-
-
SELECT id,name FROM employees LIMIT 3,10;
id name
------------------------
13 Pepe
22 Manolo
34 Jose
...
Necesitamos el total de filas para calcular cuantos paquetes de datos (paginas) necesitamos. En este caso tenemos un total de 63 filas paginadas de 10 en 10. Veamos un script PHP muy simple que nos devuelve las paginas:
-
-
$pagging = 10;
-
isset($_GET[‘page’]) ?
$current =
(int
)$_GET[‘page’] :
$current =
1;
-
-
-
-
-
$total_pages =
(int
)ceil($row[‘total’]/
$pagging);
-
-
//header pagging
-
echo "Pagina $current de $total_pages ({$row['total']} resultados)nn";
-
-
//body pagging
-
$q = msql_query("SELECT id,name FROM employees LIMIT {$current-1},$pagging;");
-
-
{
-
-
}
-
-
//footer pagging
-
-
for($i=0;$i < $total_pages;$i++)
-
{
-
if($i === ($current-1))
-
-
else
-
-
}
-
Pagina 1 de 7 (63 resultados)
13 Pepe
22 Manolo
34 Jose
...
« [1] 2 3 4 5 6 7 »
Consideraciones para MySQL
Para calcular el paginado debemos utilizar obligatoriamente dos consultas como mínimo, si las consultas son complejas podemos tener un problema de rendimiento importante pero existen maneras de atajar el camino. Si la base de datos que utilizamos es MySQL existe un hack para devolver el resultado paginado y el total de filas en un solo paso.
Seleccionamos la pagina que nos interesa:
-
-
SELECT SQL_CALC_FOUND_ROWS id,name FROM employees LIMIT 0,10;
Miramos cuantas filas tiene en total:
Siguen siendo dos consultas pero a efectos prácticos rinde como “una y media”, en cualquier caso nunca será tan lento como hacerlo con las dos consultas anteriores.
SQL_CALC_FOUND_ROWS funciona a partir de la versión 4 de MySQL si no recuerdo mal.
Las instrucciones SQL_CALC_FOUND_ROWS y FOUND_ROWS() deben ir una detrás de otra, si hacemos consultas por en medio es muy probable que nos encontremos con resultados imprecisos.
Me consta que Oracle tiene una instrucción equivalente pero no sabría explicaros, no tengo suficiente experiencia con esa base de datos. PostgreSQL y SQLite si que sé seguro que no pueden hacer uso de este hack, al menos en sus versiones actuales.
Paginado deluxe en CakePHP
Los usuarios de CakePHP estamos de suerte, Andy Dawson escribió un helper que permite realizar paginados de cualquier tipo de datos con muy poco esfuerzo. Permite ordenar datos tabulados por columnas y el interfaz de control funciona completamente en AJAX (si el cliente lo permite o nosotros lo forzamos).
Encontrareis todo lo referente al paginado en Bakery.
menéame
