Los índices en base de datos son un tema mitológico para muchos desarrolladores: hemos escuchado que existen y tenemos una idea vaga acerca de las ventajas que proporcionan pero pareciera que el conocimiento necesario detrás de ellos nos impide realmente aprovecharlos. La vida sin embargo tiene una forma cruel de mostrarnos la necesidad de utilizarlos y es cuando nos damos cuenta que el rendimiento de nuestros queries en ambientes productivos es completamente distinto a nuestros entornos de desarrollo.

Luego de finalizada la aventura de automatizar la carga de varios millones de registros a nuestra base de datos de precios de acciones de mercados financieros que nos ocupó hace algunos días no fue ninguna sorpresa encontrar que los tiempos de respuesta en nuestra tabla principal de precios al hacer queries básicos era francamente apabullante. Y para cualquier tabla con una cantidad de datos similar el resultado va a ser el mismo independientemente del servidor y los recursos que se cuenten, este es el tipo de problemas que no se pueden resolver con fuerza bruta o agregando recursos de forma indiscriminada.

Antes de empezar a crear índices, vale la pena dejar claro el concepto clave acerca de un índice. Pensemos por un momento en que nuestra tabla es un gigantesco directorio telefónico, y para quienes sean demasiado jóvenes para saber lo que es un directorio telefónico sencillamente digamos que es un libro con los números de teléfono de toda una ciudad de 16 millones de habitantes. Estos números de teléfonos están asociados a un nombre y apellido correspondiente, pero con la salvedad de que fueron impresos en el orden en que se fueron agregando y por lo tanto encontrar un nombre específico es una tarea que consume enormes recursos y tiempo puesto que no hay ninguna forma de facilitar una búsqueda sistemática.

Si quisiéramos encontrar a Juan Pérez, nuestro instinto nos dice que debiera existir un índice por orden alfabético y debiéramos sencillamente ubicar la sección donde empieza la letra P y partir de allí. Los índices en la base de datos cumplen una función muy similar. De la misma manera en que sabemos que la manera más fácil de ubicar un nombre dentro de un listado de millones de registros es empezando por el apellido, en nuestro caso tenemos una tabla con registros diarios de precios para cualquier cantidad de acciones.

Uno de los errores más comunes cuando se trabaja con índices es creer que puesto que los índices aceleran las consultas es buena idea declarar todos los campos de una tabla como índice. Esto no funciona y causa irónicamente el resultado contrario a lo que estamos buscando: nuestras consultas se vuelven aún más lentas. Debemos identificar el menor número posible de campos necesarios para la creación de un índice, y con ya muchos años de experiencia bajo el brazo puedo contar una o dos veces donde fue necesario incluir más de un campo como parte de un índice. Adicionalmente, es necesario entender que cada índice crea un registro lógico que ocupa bastante espacio en disco dependiendo del tamaño de nuestra tabla, por lo que crear índices innecesarios consumirá no sólo más recursos de almacenamiento sino obligará al motor de base de datos a recorrer varios índices hasta encontrar el registro buscado, lo que representa muchísimo tiempo antes de ver un resultado en pantalla. Finalmente, crear un índice no cambia la ubicación física de los registros en la tabla original por lo que cada vez que se inserta, actualiza o elimina un registro, adicionalmente a modificar la tabla original el motor de base de datos se ve obligado a mantener actualizado el índice asociado a dicha tabla por lo que es una razón más para mantener nuestros índices al mínimo.

Una vez dicho lo anterior, refresquemos la memoria acerca de nuestra tabla de precios:

El campo stock es una llave foránea hacia nuestra tabla de acciones. Por lo demás todo es local. Nuestra meta con este ejercicio es crear una consulta donde le indiquemos a la base de datos que deseamos recuperar los precios para un símbolo (ticker) en un período de tiempo dado. He diseñado el query de la siguiente manera tomando en cuenta que hay un JOIN hacia la tabla stock para recuperar esta información aún sin saber el identificador del símbolo que estamos buscando.

SELECT `price`.`date`, `stock`.`ticker`, `price`.`close`
  FROM `stock_price` AS `price`
  JOIN `stock` ON `price`.`stock` = `stock`.`id`
 WHERE `price`.`date` BETWEEN DATE('2017-01-01') AND DATE('2017-12-31')
   AND `stock`.`ticker` = 'AAPL'
ORDER BY `price`.`date` ASC;

El query por naturaleza no es complicado, sin embargo la cantidad de información que el motor de base de datos debe comparar antes de devolver las tuplas solicitadas hace que nuestra consulta no sea óptima. Acá es donde los índices se vuelven una herramienta muy poderosa. Es fácil creer que el punto de entrada de este query es el símbolo (AAPL – Apple Inc), sin embargo le precede un dato aún más importante: la fecha. Cualquier consulta que hagamos hacia este conjunto de registros sin importar el símbolo que estemos buscando irá siempre ordenado por fecha, no tiene sentido hacerlo de otra manera. Es por lo tanto evidente que nuestro punto de entrada debe ser la fecha y el símbolo. Y esto es uno de los secretos de los índices, deben ser analizados desde un punto de vista de los queries que harán uso de ellos.

Crear un índice es tan sencillo como modificar la tabla de destino e indicar el campo sobre el que se creará el índice:

ALTER TABLE `stock_price` 
    ADD INDEX `idx_stock_price` (`date`, `stock`);

Hasta acá todo muy sencillo, sin embargo nuestro query no consulta mediante un identificador de símbolo sino el símbolo propiamente dicho, es por lo tanto necesario crear un índice también en nuestra tabla de acciones para que cualquier búsqueda con el símbolo sea igual de rápida:

ALTER TABLE `stock`
    ADD INDEX `idx_stock_ticker` (`ticker`);

El momento de la verdad es cuando finalmente ejecutamos nuestro query y obtenemos el resultado esperado:

Sin embargo lo que estamos buscando no es sólo recuperar la información sino recuperarla en un tiempo óptimo.

Recuperar 251 registros es pan comido para cualquier motor de base de datos. Recuperar 251 registros específicos de una tabla con más de 16 millones de registros en fracciones de segundo es algo completamente distinto. Hemos creado un índice que nos permite hacer consultas a una tabla con un volúmen considerable de información y aún así obtener resultados en fracción de segundos. Esta es la verdadera magia de los índices, es una herramienta muy poderosa que debe estar siempre en el arsenal de cualquier desarrollador y administrador de base de datos.