Paginación: OFFSET o keyset
IntermedioPara mostrar resultados por páginas hay dos técnicas. LIMIT n OFFSET m salta las primeras m filas: es simple, pero cada página es más lenta que la anterior. La paginación por clave (keyset) pide las filas que vienen después de la última que se mostró, con WHERE (clave, id) < (valores de la última fila).
Sintaxis
-- Por OFFSET: página 3 de 10 filas
ORDER BY clave DESC, id DESC
LIMIT 10 OFFSET 20;
-- Por clave: las 10 filas que siguen a la última mostrada
WHERE (clave, id) < (:clave_ultima, :id_ultimo)
ORDER BY clave DESC, id DESC
LIMIT 10;Ejemplo
SELECT
r.id,
r.name,
to_char(r.joined_at, 'YYYY-MM-DD') AS fecha_alta
FROM restaurants AS r
WHERE (r.joined_at, r.id) < (DATE '2024-02-26', 351)
ORDER BY r.joined_at DESC, r.id DESC
LIMIT 5;Resultado
| id | name | fecha_alta |
|---|---|---|
| 273 | El Rincón Rivera 273 | 2024-02-25 |
| 262 | Casa Silva 262 | 2024-02-25 |
| 163 | Sabor Gómez 163 | 2024-02-25 |
| 80 | La Esquina González 80 | 2024-02-24 |
| 223 | Don Mamani 223 | 2024-02-23 |
Cómo leerlo
Es la página 2 del directorio ordenado del restaurante más nuevo al más antiguo. La página 1 terminó en el restaurante 351, dado de alta el 2024-02-26, y la consulta pide lo que viene después de esa fila. Da lo mismo que LIMIT 5 OFFSET 5. Tres restaurantes comparten la fecha 2024-02-25: por eso la clave incluye id, que desempata y garantiza que ninguna fila se repita ni se pierda entre páginas.
Error común
Así no
SELECT r.id, r.name
FROM restaurants AS r
ORDER BY r.joined_at DESC
LIMIT 10 OFFSET 10000;Dos problemas. Sin desempate único, las filas con la misma fecha pueden cambiar de página entre una consulta y otra. Y con un OFFSET grande el motor igual tiene que leer y descartar las 10000 filas anteriores. Además, si entra un registro nuevo mientras alguien navega, todo se corre un lugar y una fila aparece dos veces. Keyset no tiene esos problemas, pero no permite saltar directo a la página 50.
En otros motores: SQL Server, Oracle, MySQL
SQL ServerOracle
Paginan con
OFFSET … ROWS FETCH NEXT … ROWS ONLY(SQL Server desde 2012, Oracle desde 12c). En SQL Server es obligatorio que hayaORDER BY.ORDER BY joined_at DESC, id DESC OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;MySQL
Acepta
LIMIT 5 OFFSET 5y la forma cortaLIMIT 5, 5, donde el primer número es el desplazamiento y el segundo la cantidad de filas.SQL Server
No permite comparar pares como
(joined_at, id) < (…). La condición de keyset se escribe desarmada, conOR.WHERE joined_at < '2024-02-26' OR (joined_at = '2024-02-26' AND id < 351)
Estos fragmentos no se ejecutan en el curso: se muestran como referencia de cada motor.
Ejemplo que no se ejecuta en el curso
CREATE INDEX restaurants_joined_at_id_idx
ON restaurants (joined_at, id);La paginación por clave es rápida cuando existe un índice sobre las mismas columnas del ORDER BY: el motor va directo a la fila de corte y lee solo las siguientes. En el sandbox del curso no se pueden crear índices; esto es lo que harías en una base propia.