Tengo una mesa como esta.
parent VARCHAR PK priority INT PK child VARCHARAhora necesito SELECCIONAR/CONTAR, para la paginación de primavera, aquellos que tienen más hijos que el número especificado.
Aquí vienen mis consultas.
-- SELECT SELECT parent, GROUP_CONCAT(child ORDER BY priority SEPARATOR 0x1D) AS concatenated_children, COUNT(child) AS child_count FROM mytable GROUP BY parent HAVING child_count >= ?1 ORDER BY parent ASC LIMIT 2048,? -- GETTING SLOWER when the OFFSET is increases -- COUNT SELECT COUNT(wrk.parent) FROM (SELECT parent, COUNT(child) AS child_count FROM mytable GROUP BY parent HAVING child_count >= ?1) AS wrk La parte ?1 es una constante ( 8 ) y la parte compensada ( ,? ) aumenta por paginación.
El problema es que la consulta de selección se vuelve más lenta a medida que aumenta el número de página.
?size=2048,page=0 took 60 ms ?size=2048,page=100 took 2646 ms¿Cómo puedo arreglar esto?
CREATE TABLE `mytable` ( `parent` varchar(100) NOT NULL, `child` varchar(255) NOT NULL, `priority` int(11) NOT NULL, PRIMARY KEY (`parent`,`priority`) ) parent child priority --------------------------------- NIKE Air Max 1 NIKE Air Jordan 2 WATCH Patek Philippe 1 WATCH Rolex 2Criterios
Select all parents (and its children) which each has at least some number children, say 8.La desaceleración ocurre debido a la forma en que funciona OFFSET : obtiene todos los datos y solo luego suelta la parte antes de la compensación. Para su caso, significa que la agrupación ocurrirá no solo para la página actual, sino también para todas las páginas anteriores.
El truco estándar para solucionar este tipo de problema es usar Keyset Pagination . Al recuperar la página, debe recordar su último parent . Luego, para obtener la página siguiente, usa su consulta con el
WHERE parent > YOUR_LAST_PARENT cláusula y sin OFFSET . El RDBMS verá que el parent está indexado y navegará rápidamente por el índice hasta el parent correcto.
Consulta completa :
SELECT parent, GROUP_CONCAT(child ORDER BY priority SEPARATOR 0x1D) AS concatenated_children, COUNT(child) AS child_count FROM mytable WHERE parent > ?1 GROUP BY parent HAVING child_count >= ?2 ORDER BY parent LIMIT 2048 Al consultar la primera página, deberá ejecutar esta consulta sin la cláusula WHERE .
Sus parámetros de URL ahora se verán como
?size=2048,parent=PARENTXXX Por supuesto, tendrá que renunciar a la paginación integrada de Spring Data JPA. Tampoco es posible saltar inmediatamente a una página en particular, básicamente estás limitado a las páginas siguientes/anteriores (siempre que recuerdes la página parent anterior).
Por cierto, al experimentar con esto observé que el mayor problema con la paginación Spring Data JPA es que tiene que COUNT todas las filas. Para InnoDB, solo eso lleva más tiempo que buscar la página.
EDITAR La primera versión de la respuesta recomendaba agregar la condición de parent a la cláusula HAVING . Como se menciona en los comentarios a continuación, no funcionará (probé que no funciona).
Como ya se mencionó, OFFSET debe obtener y lanzar todas las filas antes de las deseadas.
Si puede codificar la consulta para "recordar dónde la dejó", hay una manera mucho más rápida que usar OFFSET .
Aquí hay una discusión específica de MySQL de tal: http://mysql.rjweb.org/doc.php/pagination