Un concepto muy pero muy importante en Firebird es lo que se llama “selectividad de los índices”.

¿Qué significa eso?

Los índices sirven para dos cosas:

  • Para buscar un valor en una tabla
  • Para obtener los datos de una consulta ordenados

Pero tienen sus desventajas también:

  • Ocupan espacio en la Base de Datos (y por lo tanto en el disco duro)
  • Agregarle, borrarle o modificarle un dato a un índice toma su tiempo, no es instantáneo. Si la tabla tiene pocas filas entonces sus índices también serán pequeños y las inserciones rapidísimas pero si la tabla es grande (varias millones de filas)  los índices serán gigantescos e insertarles nuevos datos será bastante más lento

El peor índice, el inútil, el que para nada sirve es aquel que tiene todos sus valores idénticos. Por ejemplo, una tabla tiene una columna llamada SUCURSAL, esa tabla tiene millones de filas, pero en todas esas filas la columna SUCURSAL tiene el valor de cero. Por lo tanto, un índice según la columna SUCURSAL es totalmente inútil.

El mejor índice, el perfecto, es el que tiene todos sus valores distintos. Esa es justamente una de las grandes ventajas de tener Primary Keys e Unique Keys, que sus índices están garantizados de tener todos los valores distintos.

Un índice muy malo es el que tiene pocos valores distintos. Por ejemplo en una tabla de PERSONAS hay una columna para guardar el sexo (masculino o femenino) de cada persona. Se crea un índice sobre esa columna. Pésima idea.

La selectividad de un índice es un número que nos dice cuan efectivo es ese índice y se calcula según esta fórmula:

Selectividad = 1 / Cantidad_de_filas_con_valores_distintos

La mejor selectividad que podría tener un índice se calcula según esta fórmula:

Mejor_Selectividad = 1 / Cantidad_total_de_filas_de_la_tabla

Cuanto más se acerque la selectividad a la mejor selectividad posible, mucho mejor. Veamos algunos ejemplos en una tabla según la columna NOMBRE:

ALICIA, GRACIELA, SUSANA                     Selectividad = 1 / 3 = 0,33   Mejor = 1 / 3
ALICIA, GRACIELA, MIRTHA, SUSANA             Selectividad = 1 / 4 = 0.25   Mejor = 1 / 4
ALICIA, GRACIELA, GRACIELA, MIRTHA, SUSANA   Selectividad = 1 / 4 = 0,25   Mejor = 1 / 5 = 0,20

En el tercer ejemplo la selectividad es de 0,25 (porque hay 4 valores distintos) la cual no es la mejor posible, la mejor posible hubiera sido 0,20 si todos los valores eran distintos. Recuerda que cuanto más se acerque la selectividad a la mejor selectividad posible es mucho mejor.

Cuando la selectividad no es igual a la mejor selectividad posible (como en el tercer ejemplo) eso significa que hay valores repetidos. Y cuantos más valores repetidos haya, mucho peor.

¿Cuál es la conclusión?

Que los buenos índices tienen muy pocos valores repetidos (el mejor caso posible es cuando ningún valor está repetido) y que no tiene sentido crear índices según columnas con muchos valores repetidos porque solamente harán perder tiempo cada vez que se inserte/borre/modifique una fila y no acelerarán las búsquedas ni las consultas.

IMPORTANTE: El Firebird guarda en la Base de Datos la selectividad de cada índice y usa ese número para decidir si usar el índice o no cuando haces una búsqueda o una consulta. Para asegurarte que la selectividad que usa es la correcta debes escribir:

SET STATISTICS INDEX MiIndice

después de haber insertado/borrado/modificado una gran cantidad de filas (por ejemplo, más de 20.000)

Si quieres de una sola vez recalcular las estadísticas de todas las tablas puedes escribir:

SET TERM ^ ;

CREATE PROCEDURE ACTUALIZAR_ESTADISTICAS_INDICES
AS
DECLARE VARIABLE lcNombreIndice VARCHAR(31);
BEGIN

FOR SELECT RDB$INDEX_NAME FROM RDB$INDICES INTO :lcNombreIndice DO
   EXECUTE STATEMENT 'SET STATISTICS INDEX ' || :lcNombreIndice || ';' ;

END^

SET TERM ; ^

El Firebird también actualiza todas las estadísticas de los índices cuando creas el índice o cuando restauras un backup.