MySQL/MariaDB el índice en tabla NO Funciona

En uno de los distintos sistemas en que he colaborado, existe un proceso que se ejecuta en segundo plano cada 10 minutos, encargado de regresar información de un sistema a un ERP; esta crónica se deriva de un reporte de error por parte de uno de los usuarios finales, siendo el reporte:

“El proceso no está enviando la información al ERP”

La gran mayoría de los reportes de error de los usuarios tienen esta particular característica de ser extremadamente generales y carecer de datos básicos para saber por donde empezar, por ejemplo la fecha/hora (aproximada o exacta) cuando se percibió el error, si es de carácter general o se observa solo para algunos casos.

Para diagnosticar qué estaba pasando, hicimos la revisión de rutina (para fines de referencia, el servicio en segundo plano será simplemente “el servicio” en adelante):

¡Todo estaba en su sitio! Entonces, ¿por qué no estaba funcionando?

Haciendo una revisión más detallada, encontramos que los eventos en el log estaban teniendo registro cada 15 minutos, uno detrás de otro (un tiempo de ejecución extremadamente extenso y anormal). El programa primero cuenta los registros que debe procesar, para después, ejecutar la rutina de envío con subconjuntos más pequeños en N iteraciones. Una vez contados los registros, se obtienen los subconjuntos de datos pendientes de envío con un query a base de datos, y el resultset se va recorriendo secuencialmente para hacer la transferencia de datos al ERP.

El conteo tomaba 15 minutos en ejecutar, y el query para obtener los detalles, otros 15 minutos más, añadiendo a estos ya excesivos 30 minutos, el tiempo que implica transferir transacción por transacción. El usuario estaba frustrado y molesto, con sobrada razón.

El query protagonista de la crónica es el siguiente:

SELECT t1.id_trx,
t1.serie,
t1.folio,
t2.uuid,
t2.id_trx33,
t1.id_tipo_de_comprobante,
t3.id_control
FROM control_t3 t3
INNER JOIN t1 ON t3.trx_num = t1.id_trx
INNER JOIN t2 ON t1.id_trx33_r = t2.id_trx33
WHERE t3.status = 0 AND t2.uuid IS NOT NULL

Validamos cada parte del select. Las tablas están siendo unidas por llaves primarias/foráneas con su correspondiente índice, de tal suerte que incluso sin usar filtros y traer la totalidad de los registros, la consulta ejecutaba en milisegundos. Mediante una prueba exhaustiva reforzada con el EXPLAIN de la consulta, se determinó que el problema se presentaba al usar el filtro t3.status = X (donde X es el estatus buscado).

La tabla t3 tiene un índice compuesto con las columnas trx_num y status, y sin embargo, cuando se agregaba la cláusula where, el query tomaba 15 minutos en completar, ya que la búsqueda filtrando por la columna status se hacía de forma secuencial en toda la tabla.

A primera vista, da la impresión de que el motor de la base de datos simplemente decidió ignorar el índice que para el ojo espectador, resulta obvio que debería entrar en acción.

Entonces, ¿si existe el índice de forma explícita, por qué el motor está optando por hacerlo secuencialmente?

La clave de este dilema recae en la forma como está escrito el query y LA ESTRUCTURA de la tabla de base de datos. Resulta ser, que probablemente en un momento de debilidad, quien diseñó la tabla, generó el campo status (que fue el primer inconveniente, porque status es una palabra reservada…) de tipo VARCHAR(1), en lugar de usar un tipo numérico.

Cuando se hace la consulta de la forma:

t3.status = 0

El motor tiene que hacer la conversión (automática) de los datos en la tabla, de varchar a un tipo numérico antes de poder comparar cada valor (todos los de la tabla) con un 0 [cero numérico], por eso es que el motor realizaba la consulta registro por registro.

Excelente, perfecto, buena historia… ¿Cómo se corrige?

La efectiva

Se corrige la estructura de la tabla para convertir ese VARCHAR(1) por un SMALLINT, INT (o el dato que convenga) y se regenera el índice en turno.

Esto no lo hicimos porque los datos que terminan en esa tabla son cargados por un programa distinto del que transfiere los datos al ERP, y en ese momento no podíamos estar seguros si cambiar el tipo de dato afectaría el funcionamiento del mismo.

La económica (y que aplicó)

Se corrige el query para que la comparación del dato se realice utilizando el tipo de dato correspondiente, es decir:

t3.status = '0'

De esta forma el motor puede utilizar el índice creado para tales efectos.

El query quedó finalmente así:

SELECT t1.id_trx,
t1.serie,
t1.folio,
t2.uuid,
t2.id_trx33,
t1.id_tipo_de_comprobante,
t3.id_control
FROM control_t3 t3
INNER JOIN t1 ON t3.trx_num = t1.id_trx
INNER JOIN t2 ON t1.id_trx33_r = t2.id_trx33
WHERE t3.status = '0' AND t2.uuid IS NOT NULL

Y así termina esta crónica, con una pregunta en la mente de un servidor, para quien sea que haya optado por hacer la columna status alfanumérica (ya no digamos con ese nombre).