sql - soporta - Ejecute una consulta con un LIMIT/OFFSET y también obtenga el número total de filas

optimizar consultas lentas mysql

Question

Para propósitos de paginación, necesito ejecutar una consulta con las cláusulas LIMIT y OFFSET . Pero también necesito un recuento del número de filas que devolvería esa consulta sin las cláusulas LIMIT y OFFSET .

Quiero correr:

SELECT * FROM table WHERE /* whatever */ ORDER BY col1 LIMIT ? OFFSET ?

Y:

SELECT COUNT(*) FROM table WHERE /* whatever */

Al mismo tiempo. ¿Hay alguna forma de hacerlo, en particular una forma que permita a Postgres optimizarlo, para que sea más rápido que ejecutar ambos individualmente?

Answer 1

No.

Quizás haya una pequeña ganancia que teóricamente podría obtener al ejecutarlos individualmente con suficiente maquinaria complicada debajo del capó. Pero, si desea saber cuántas filas coinciden con una condición, tendrá que contarlas en lugar de solo un subconjunto LIMITADO.

Answer 2

Sí. Con una simple función de ventana:

SELECT *, count(*) OVER() AS full_count
FROM   tbl
WHERE  /* whatever */
ORDER  BY col1
LIMIT  ?
OFFSET ?

Tenga en cuenta que el costo será sustancialmente mayor que sin el número total, pero aún más barato que dos consultas separadas. Postgres tiene que contar realmente todas las filas en cualquier caso, lo que impone un costo en función del número total de filas que califican. Detalles:

• La mejor manera de obtener el recuento de resultados antes de aplicar LIMIT

Sin embargo , como señaló Dani , cuando OFFSET es al menos tan grande como el número de filas devueltas desde la consulta base, no se devuelven filas. Así que tampoco conseguimos el full_count .

Si eso no es aceptable, una posible solución que siempre devuelve el recuento completo sería con un CTE y un OUTER JOIN :

WITH cte AS (
   SELECT *
   FROM   tbl
   WHERE  /* whatever */
   )
SELECT *
FROM  (
   TABLE  cte
   ORDER  BY col1
   LIMIT  ?
   OFFSET ?
   ) sub
RIGHT  JOIN (SELECT count(*) FROM cte) c(full_count) ON true;

Obtiene una fila de valores NULL con el full_count anexado si OFFSET es demasiado grande. O se adjunta a cada fila como en la primera consulta.

Si una fila con todos los valores NULL es un posible resultado válido, debe verificar offset >= full_count para desambiguar el origen de la fila vacía.

Esto todavía ejecuta la consulta base solo una vez. Pero agrega más sobrecarga a la consulta y solo paga si eso es menos que repetir la consulta base para el recuento.

Si los índices que admiten el orden de clasificación final están disponibles, podría ser conveniente incluir ORDER BY en el CTE (redundantemente).