Mejorar la performance de los filtros sobre columnas indexadas
Es bien sabido que el uso de filtros en nuestras consultas mejoran la performance de la misma. Podemos filtrar filas usando las cláusulas WHERE, HAVING; filtramos columnas asegurándonos de especificar solamente las columnas que realmente se necesita traer de la base de datos. Algo más, evite usar * con los SELECT!, evítelo!.
Aquí no termina todo, podemos seguir mejorando la performance de las consultas mediante el uso de índices sobre las columnas implicadas, no usarlo implica problemas de performance significantes a largo plazo. Los índices permiten traer la data mucho más rápido de la base de datos, y además el optimizador de consultas podría usarlo para realizar un filtro inicial en vez de usar la data de la tabla actual que se estuviera consultando. Bien, una manera de crear un índice es definiendo un primary key, el cual asegura que la clave sea única en la tabla. Un primary key puede estar montado sobre una sóla columna o varias columnas.
Al crear un primary key sobre varias columnas, automáticamente se crea un índice compuesto, bien, el orden de las columnas implicadas es muy importante!, y es esto lo que trataré de explicar. El sistema de base de datos utiliza los índices compuestos como (Campo1, Campo2,Campo3) en la medida en que puede encontrar lo que quiere para un índice leyendo los campos clave de izquierda a derecha. En base a lo dicho, si se tiene un índice Idx1(Campo1, Campo2, Campo3), y se necesitara un índice para Campo1, podría usarse este mismo índice, y estaría bien!. Pero si se desea un índice para Campo2, este índice sería inefectivo, afectando la performance de la consulta, porque Campo1 va antes que Campo2. Cuando usemos estas columnas, debemos asegurarnos usarlo en ese orden, es decir, de izquierda a derecha!, para evitar problemas, y aprovechar el índice. Por ejmplo, si tenemos un indice (Campo1, Campo2) en una consulta algo así como:
SELECT campo3
FROM table1
WHERE campo1 > 150
AND campo2 = 'Trujillo'
Aquí se estaría aprovechando y usando correctamente el índice. Pero que pasa si tengo esto:
SELECT campo3
FROM table1
WHERE campo2 = 'Trujillo'
AND campo1 > 150
Desde luego, es la misma consulta, pero hay un detalle. El Detalle es que no estaría usándose el índice correctamente, o en todo caso, el optimizador de consulta podría decidir simplemente no usarlo, mientras que nosotros creemos que tenemos un índice a nuestro favor. En este caso, recomiendo crear índices separados para cada columna.
CREATE INDEX idx2 ON table1(campo1);
CREATE INDEX idx3 ON table1(campo2);
En fin, no olvide de tener mucho cuidado al momento de definir el orden de la columnas cuando va crear un índice compuesto, como también el orden en el que los usa. Espero les sirva!.
Saludos Cordiales!,