Estoy tratando de optimizar una consulta que (innecesariamente) cuenta con casi 900 000 filas en una tabla, lo que lleva demasiado tiempo.
La tabla contiene entradas de registro para eventos que tienen lugar en diferentes partes de una aplicación web, y quiero saber cuántas entradas de registro no leídas existen para cada tipo de registro cuando el recuento de filas para ese tipo es 1000 o menos, pero cuente como máximo 1001 filas si el recuento es 1001 o más.
No necesito contar más después de eso, solo generaré "más de 1000" para ese tipo de registro.
Digamos que tenemos la siguiente tabla llamada my_logs con datos:
id log_type log_text is_read 1 'Type 1' 'Text 1' 1 2 'Type 1' 'Text 2' 1 3 'Type 1' 'Text 3' 0 4 'Type 1' 'Text 4' 0 5 'Type 1' 'Text 5' 0 6 'Type 1' 'Text 6' 0 7 'Type 2' 'Text 7' 0 8 'Type 2' 'Text 8' 0En este ejemplo, mi consulta actual se vería así:
SELECT log_type, COUNT(*) AS unread FROM my_logs WHERE is_read = 0 GROUP BY log_type;
Esta consulta cuenta cada fila y proporciona la cantidad correcta de filas para cada tipo de registro, por supuesto. El problema es que cuando la tabla contiene 900 000 filas, esta es una consulta costosa, y contar más de 1000 filas de cada tipo es totalmente innecesario ya que a los usuarios no les importará la diferencia entre 1 000 y 20 000, simplemente ver muchas entradas .
Esto es lo más cerca que estuve de una solución (límite ajustado para ajustarse al ejemplo de my_logs y demostrar el uso):
SELECT log_type, COUNT(*) AS unread FROM ( SELECT log_type FROM my_logs ml1 WHERE is_read = 0 LIMIT 3 /* To display "more than 2" in webapp */ ) AS ml2 GROUP BY logtype_txt; pero esta consulta agrupa todos los log_type s en la consulta interna y lo limita a 1001 filas, que no es lo que quiero. Necesito dividir las filas en cada log_type y luego contar un máximo de 1001 filas. La salida que quiero en este ejemplo sería:
log_type unread 'Type 1' 3 'Type 2' 2Esta pregunta y esta pregunta discuten cómo dejar de contar cuando se encuentran n filas, pero no tienen en cuenta la agrupación que necesito.
¿Alguien sabe alguna solución?
Esta respuesta no funciona en MariaDB o MySQL.
La respuesta que está buscando se basa en una "expresión de tabla lateral". Esto se implementa en Oracle, DB2, PostgreSQL y SQL Server.
Aquí está la consulta que sería óptima en términos de filas leídas de la tabla, en PostgreSQL:
select x.log_type, count(yz) from ( select distinct log_type as log_type from my_log ) x left join lateral ( select 1 as z from my_log b where b.log_type = x.log_type and is_read = 0 limit 2 + 1 ) y on true group by x.log_typeVer ejemplo de ejecución en DB Fiddle .
Las consultas laterales se ejecutan una vez de acuerdo con los valores disponibles en la expresión de la tabla colocada antes de ellas. EN este caso, la expresión de la tabla x producirá todos los valores diferentes para log_type (usando el índice para el rendimiento). Entonces la consulta lateral se ejecutará una vez por cada valor de x , con un LIMIT de 3 (en este caso). Finalmente, la consulta cuenta cuántos valores z se encontraron.
Como puede ver, el proceso anterior solo lee un máximo de 3 filas por tipo.
Consulte el LIMIT ROWS EXAMINED EXAMINADAS de MariaDB-5.5.21:
https://mariadb.atlassian.net/browse/MDEV-28
Debe ser exactamente lo que estás pidiendo.
(No creo que esté disponible en MySQL).