En el desarrollo de bases de datos, es crucial comprender cómo filtrar y optimizar las consultas. Dos cláusulas SQL comúnmente utilizadas para el filtrado son WHERE y HAVING. Además, el uso eficiente de índices en MySQL es fundamental para el rendimiento.

Cláusula WHERE

La cláusula WHERE se utiliza para filtrar registros antes de que se realice cualquier agrupación.

Flujo de ejecución

El orden general de ejecución en una consulta SQL que involucra agrupación es:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

Ejemplo de uso de WHERE

SELECT *
FROM empleado
WHERE salario > 5000;

Este ejemplo selecciona todos los campos de la tabla empleado donde el valor de la columna salario es mayor que 5000. El filtrado se aplica a cada fila individualmente.

Cláusula HAVING

La cláusula HAVING se utiliza para filtrar los resultados después de que se ha realizado la agrupación y se han calculado las funciones de agregación.

Ejemplo de uso de HAVING

SELECT dept, COUNT(*)
FROM empleado
GROUP BY dept
HAVING COUNT(*) > 2;

Este ejemplo agrupa los empleados por departamento y luego filtra estos grupos, mostrando solo aquellos departamentos que tienen más de 2 empleados. La lógica es agrupar primero por dept, calcular el recuento para cada grupo, y luego aplicar el filtro COUNT(*) > 2.

Diferencias clave entre WHERE y HAVING

Ambas cláusulas son para filtrar, pero operan en diferentes etapas del procesamiento de la consulta:

¿Por qué HAVING necesita GROUP BY?

HAVING está diseñado para filtrar los resultados de las agregaciones (como COUNT(), SUM(), AVG(), etc.). Estas agregaciones solo tienen sentido y se calculan después de que los datos han sido agrupados por una o más columnas. Por lo tanto, HAVING opera sobre los resultados de la agrupación.

Flujo de ejecución detallado

1. FROM: Identifica la tabla de origen.
2. WHERE: Filtra las filas individuales según las condiciones especificadas.
3. GROUP BY: Agrupa las filas restantes según las columnas especificadas.
4. Cálculo de funciones de agregación: Se aplican funciones como COUNT(), SUM(), etc., a cada grupo.
5. HAVING: Filtra los grupos resultantes basándose en las condiciones de agregación.
6. SELECT: Selecciona las columnas y los resultados de agregación para la salida final.
7. ORDER BY: Ordena los resultados finales.

En resumen, WHERE filtra datos crudos (filas), mientras que HAVING filtra datos agregados (grupos).

Cuándo usar WHERE y cuándo usar HAVING

La regla general es: si es posible, use WHERE en lugar de HAVING. Esto se debe a que WHERE opera en un conjunto de datos más pequeño (antes de la agrupación), lo que resulta en un procesamiento más eficiente.

Ejemplo combinado y eficiente

SELECT dept, COUNT(*)
FROM empleado
WHERE salario > 5000
GROUP BY dept
HAVING COUNT(*) > 2;

En este ejemplo, la cláusula WHERE primero filtra a los empleados con un salario mayor a 5000. Luego, el conjunto de datos reducido se agrupa por departamento. Finalmente, HAVING filtra los departamentos que, tras la agrupación, todavía tienen más de 2 empleados. Este enfoque es más eficiente porque la agrupación y la agregación se realizan sobre un subconjunto de datos más pequeño.

Índices en MySQL

Los índices son estructuras de datos esenciales en MySQL para acelerar la recuperación de datos. Se pueden comparar con el índice de un libro: sin él, tendrías que hojear cada página (escaneo completo de tabla); con un índice, puedes localizar la información deseada rápidamente.

Funciones principales de los índices

1. Aceleración de consultas: El beneficio más significativo es evitar escaneos completos de tabla, permitiendo a MySQL localizar filas específicas de manera eficiente.
2. Optimización de ORDER BY y GROUP BY: Un índice en las columnas utilizadas en ORDER BY o GROUP BY puede eliminar la necesidad de crear tablas temporales y realizar operaciones de ordenamiento o agrupación costosas.
3. Restricción de unicidad: Los índices, como los índices primarios y únicos, garantizan la unicidad de los valores en una columna o conjunto de columnas (por ejemplo, asegurando que no haya dos usuarios con el mismo número de teléfono).

Consideración importante: Los índices mejoran la velocidad de lectura (SELECT) pero pueden ralentizar las operaciones de escritura (INSERT, UPDATE, DELETE) porque el índice también debe ser actualizado. Por lo tanto, no se trata de crear tantos índices como sea posible, sino de crearlos de manera estratégica según las necesidades de la aplicación.

Clasificación de índices

Los índices se pueden clasificar de varias maneras:

• Por funcionalidad: Índice primairo (PRIMARY KEY), Índice único (UNIQUE), Índice de texto completo (FULLTEXT), Índice normal (o deja, INDEX).
• Por estructura de datos: Índice B+ Tree, Índice Hash.
• Por ubicación de almacenamiento: Índice Clustered, Índice Non-Clustered.

Tipos de índices comunes y sus características

• Un índice PRIMARY KEY es esencialmente un índice UNIQUE que además tiene la restricción de no ser NULL.
• Los índices normales (INDEX) solo aceleran las búsquedas y no imponen unicidad. Son útiles en campos con alta frecuencia de escritura o en rangos de consulta.
• Los índices únicos (UNIQUE) fuerzan la unicidad de los datos y se verifican durante las operaciones de inserción y actualización. Son ideales para mantener la integridad de datos en campos que deben ser únicos por lógica de negocio.

Estructura subyacente de los índices

La gran mayoría de los índices en MySQL (PRIMARY KEY, UNIQUE, INDEX, índices compuestos) se implementan utilizando la estructura de datos B+ Tree.

• Características del B+ Tree:
  • Todos los datos se almacenan en los nodos hoja, que están enlazados secuencialmente. Esto facilita las búsquedas por rango (ej. WHERE id BETWEEN 10 AND 20).
  • Los nodos no hoja solo contienen claves de índice, no datos completos. Esto permite que el árbol sea más "bajo y ancho", reduciendo la cantidad de accesos a disco (I/O) necesarios para encontrar un dato.
• Comparación con Índices Hash: Los índices Hash (utilizados por el motor de MEMORY) son extremadamente rápidos para búsquedas de igualdad (=), pero no soportan búsquedas por rango, ordenamiento, o búsquedas de prefijo. Los B+ Trees, en cambio, ofrecen un buen rendimiento tanto para igualdad como para rangos y ordenamiento.

Escenarios comunes de ineficacia de índices

Incluso con índices, ciertas prácticas pueden hacer que no se utilicen:

• Incumplimiento del principio de prefijo más a la izquierda: Para un índice compuesto en (col1, col2, col3), una consulta que solo usa col2 o col3, o col2, col3, no usará el índice de manera óptima. La consulta debe empezar con col1.
• Aplicar funciones o realizar operaciones en la columna indexada: Consultas como WHERE SUBSTR(telefono, 1, 3) = '138' o WHERE id + 1 = 10 invalidarán el uso del índice en telefono o id respectivamente.
• Uso de comodines al inicio en búsquedas de texto: WHERE nombre LIKE '%Juan' no puede usar un índice normal. Sin embargo, WHERE nombre LIKE 'Juan%' sí puede usarlo.
• Incompatibilidad de tipos de datos: Si una columna telefono es VARCHAR y la consulta la compara con un número sin comillas (WHERE telefono = 13800138000), MySQL puede realizar una conversión implícita que impide el uso del índice.
• Uso de OR conectando campos no indexados: Una consulta como WHERE nombre = 'Ana' OR edad = 30 (si edad no tiene índice) puede hacer que el motor de base de datos decida no usar índices en absoluto.