Muchas veces necesitamos que un índice involucre a varias columnas, así que ¿cómo funcionan esos índices?

Si creamos un índice como el siguiente:

CREATE INDEX
   MiIndice1
ON MiTabla
   (MiColumna1, MiColumna2, MiColumna3);

El índice MiIndice1 utiliza los datos de 3 columnas. La primera columna (en este ejemplo MiColumna1) es la columna principal, la segunda columna (en este ejemplo MiColumna2) es dependiente de MiColumna1, o sea tiene un menor nivel, no es tan importante. Y la tercera columna (en este ejemplo MiColumna3) es dependiente tanto de MiColumna1 como de MiColumna2 y por lo tanto es la menos importante de las tres.

¿Qué significa todo esto?

Que para que en la cláusula WHERE un índice sea usado se debe especificar a MiColumna1, sí o sí.

Que si además especificamos a MiColumna2 entonces la cantidad de filas que cumplen la condición serán (generalmente) menos que en el caso anterior.

Que si además especificamos a MiColumna3 entonces la cantidad de filas que cumplen la condición serán (generalmente) menos que en el caso anterior.

Ejemplos:

En los ejemplos que vienen a continuación suponemos que MiColumna1, MiColumna2, y MiColumna3 son numéricas, pero lo mismo funcionará con columnas que tengan cualquier otro tipo de datos.

Ejemplo 1. Se utiliza el índice

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna1 = 123

Aquí se utilizará el índice MiIndice1 ¿por qué? Porque en la cláusula WHERE especificamos a MiColumna1.

Ejemplo 2. Se utiliza el índice

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna1 = 123 AND
   MiColumna2 = 456

Aquí se utilizará el índice MiIndice1 ¿por qué? Porque en la cláusula WHERE especificamos a MiColumna1 y a MiColumna2. Recuerda que si se especifica la columna MiColumna1 se usa el índice.

Ejemplo 3. Se utiliza el índice

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna1 = 123 AND
   MiColumna3 = 789

Aquí se utilizará el índice MiIndice1 ¿por qué? Porque en la cláusula WHERE especificamos a MiColumna1 y aunque no especificamos a MiColumna2 el índice igualmente fue utilizado. Recuerda que siempre que especificques a MiColumna1 se usará el índice.

Ejemplo 4. Se utiliza el índice

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna2 = 456 AND
   MiColumna1 = 123

Aquí se utilizará el índice MiIndice1 porque se especificó a la columna MiColumna1, no importa que no haya sido la primera columna escrita en la cláusula WHERE ya que el Firebird es lo suficientemente inteligente como para cambiar el orden. Para el Firebird el Ejemplo 4 y el Ejemplo 2 son idénticos, no hay diferencia entre ellos, no importa el orden en que se escriban las columnas, lo importante es que se las haya escrito.

Ejemplo 5. Se utiliza el índice

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna1 = 123 AND
   MiColumna2 = 456 AND
   MiColumna3 = 789

Aquí se utilizará el índice MiIndice1 porque se especificó a la columna MiColumna1. Como las tres columnas fueron especificadas entonces esta condición será la más rápida de todas. La cantidad de filas que cumplen la condición del WHERE es siempre la menor posible cuando todas las columnas del índice múltiple son especificadas. Por lo tanto, siempre que sea posible eso es lo que debemos hacer: especificar a todas las columnas del índice en la cláusula WHERE.

Ejemplo 6. No se utiliza el índice

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna2 = 456

Aquí no se utilizará el índice MiIndice1. ¿Por qué no? porque en la cláusula WHERE no se especificó a la columna MiColumna1 y si esa columna no se especifica el índice no se utiliza.

Ejemplo 7. No se utiliza el índice

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna2 = 456 AND
   MiColumna3 = 789

Aquí no se utilizará el índice MiIndice1. ¿Por qué no? porque en la cláusula WHERE no se especificó a la columna MiColumna1 y si esa columna no se especifica el índice no se utiliza.

Conclusión:

Crear índices de múltiples columnas puede ser muy útil en muchas ocasiones pero debemos recordar que en las claúsulas WHERE debemos siempre especificar a la primera columna de nuestro índice para que ese índice sea utilizado. Si la primera columna no es especificada, ese índice no será utilizado.

No hace falta que la primera columna del índice sea también la primera columna del WHERE porque el Firebird es lo suficiente inteligente como para (internamente) ubicar a las columnas en el mismo orden del índice.

Lo mejor que podemos hacer es que todas las columnas utilizadas en el índice sean especificadas en la cláusula WHERE, ya que así la cantidad de filas que cumplen la condición será mínima y obtendremos más rápidamente el resultado.

Artículos relacionados:

Entendiendo los índices compuestos

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios