Optimar consulta SELECT

[David_Carrera_G]

Estimados, el motivo de este tema es por una consulta SQL la cual realice hace mucho tiempo en una BD que ahora administro, el tema es que he estado falto de practica y no se me ocurre de que forma puedo optimizar la siguiente consulta:

Código SQL:

SELECT  a.*,b.moroso,
             round((datediff(curdate(),(STR_TO_DATE(LEFT(b.activacion,10),'%Y-%m-%d')))/30),0) AS MES,c.`Plan Recomendado`
 
FROM 
tbl_desconectados a
INNER JOIN arrears_paso b  ON a.customer_id = b.NRO_SUSCRIPTOR
INNER JOIN ISE_SCORE_NOV14 c ON a.customer_id = c.CLIE_CODIGO
WHERE a.ip=1
AND a.fecha_generacion = '2014-11-13'
AND a.proxy='33'
AND a.pais = 'CHI'
AND a.Contactabilidad = 1
AND b.ESTADO_PRODUCTO = 'D'
AND YEAR(CAST(b.ACTIVACION AS DATE)) = YEAR(curdate()) - 2
AND NOT a.customer_id IN (SELECT ibs FROM compromisos_chile)
ORDER BY RAND() LIMIT 200
 

Como se puede observar la consulta tiene dos partes conflictivas:

1.- cuando hago el calculo de los meses que lleva el cliente con mora(3 campo del select)
2.- Cuando defino el criterio de búsqueda, el cual debe ser dos años atrás de la fecha actual

Mi problema replica en que el total de registros que me devuelve esta tabla es de 300.000, por lo cual se demora a lo menos 15 minutos en darme el resultado esperado, habrá una forma de optimizar esta query, para reducir su tiempo de espera. Saludos y desde ya gracias.

[leonardo_josue]

Hola David_Carrera_G:

En primer lugar comienza por decirnos qué indices tienes declarados en tu tabla... te recomiendo que utilices un índice para cada uno de los campos que están involucrados en las condiciones del WHERE.

En segundo lugar, tienes una condición IN, que de entrada es uno de los operadores que peor rendimiento tienen, ya que hace una búsqueda exhaustiva... en lugar de eso puedes cambiarla por un LEFT JOIN, es decir, hacer algo como esto:

Código MySQL:

... 
FROM 
tbl_desconectados a
INNER JOIN arrears_paso b  ON a.customer_id = b.NRO_SUSCRIPTOR
INNER JOIN ISE_SCORE_NOV14 c ON a.customer_id = c.CLIE_CODIGO
LEFT JOIN (SELECT ibs FROM compromisos_chile) T ON T.ibs = a.customer_id 
WHERE 
...
AND T.ibs IS NULL
...
 

Tercero, puedes tratar de incluir algunas de las condiciones en el mismo JOIN... esto no necesariamente es más rápido, depende de las condiciones:

Código MySQL:

...
INNER JOIN arrears_paso b  ON 
   a.customer_id = b.NRO_SUSCRIPTOR AND
   b.ESTADO_PRODUCTO = 'D'
...
 

Finalmente, dale un EXPLAIN a la consulta, ahí te puedes dar cuenta si hay alguna otra cosa que puedas mejorar.

Haz la prueba y nos comentas los resultados.

Saludos
Leo

[David_Carrera_G]

Estimado, de antemano agradezco mucho tu respuesta, pero me asalta una duda luego del LEFT JOIN indicaste una clausula [AND T.ibs IS NULL] esta se te colo o lo agregaste como condición al LEFT JOIN???.

Adjunto la nueva estructura de la consulta, que gracias a tu consejo, se redujo de 5 min en ejecución a 45 segundos. Es increíble lo que consumía el IN.

Código MySQL:

SELECT a.*,b.moroso,
       round((datediff(curdate(),(STR_TO_DATE(left(b.activacion,10),'%Y-%m-%d')))/30),0) as MES,c.`Plan Recomendado`
FROM 
tbl_desconectados a
INNER JOIN arrears_paso b  ON a.customer_id = b.NRO_SUSCRIPTOR AND b.ESTADO_PRODUCTO = 'D'
INNER JOIN ISE_SCORE_NOV14 c ON a.customer_id = c.CLIE_CODIGO
LEFT JOIN (select ibs from compromisos_chile T) T ON T.ibs = a.customer_id
WHERE a.ip=1
AND a.fecha_generacion = '2014-11-13'
AND a.proxy='33'
AND a.pais = 'CHI'
AND a.Contactabilidad = 1
AND YEAR(CAST(b.ACTIVACION AS DATE)) = YEAR(curdate())- 2
ORDER BY RAND() LIMIT 200
 

Ahora adjunto print del EXPLAIN solicitado, cabe mencionar, que estas bases son transaccionales, por lo cual no poseen indices en sus tablas.

[leonardo_josue]

Hola de nuevo David_Carrera_G:

me asalta una duda luego del LEFT JOIN indicaste una clausula [AND T.ibs IS NULL] esta se te colo o lo agregaste como condición al LEFT JOIN???.

La condición va en el WHERE... como te comenté en el post pasado, una alternativa para mejorar las condiciones IN, es utilizar un LEFT JOIN... checa este ejemplo:

Código MySQL:

mysql> SELECT * FROM tabla1;
+------+----------------+
| ID   | Nombre_Usuario |
+------+----------------+
|    1 | Ramon          |
|    2 | Juan           |
|    3 | Hugo           |
|    4 | Paco           |
|    5 | Luis           |
+------+----------------+
5 rows in set (0.00 sec)
 
mysql> SELECT * FROM tabla2;
+------+-------------+
| id   | descripcion |
+------+-------------+
|    1 | Ramon       |
|    3 | Hugo        |
|    5 | Luis        |
+------+-------------+
3 rows in set (0.00 sec)
 

Supongamos que queremos obtener los registros de la tabla1 que no existen en la tabla2... de la manera en que tú lo haces sería asi:

Código MySQL:

mysql> SELECT * FROM Tabla1 WHERE id NOT IN (SELECT id FROM tabla2);
+------+----------------+
| ID   | Nombre_Usuario |
+------+----------------+
|    2 | Juan           |
|    4 | Paco           |
+------+----------------+
2 rows in set (0.00 sec)
 

El problema con este tipo de consultas, es que la subconsulta del IN se ejecuta una vez por cada registro que exista en la tabla2... es por eso que tiene tan bajo rendimiento. Al utilizar un LEFT JOIN, puedes obtener el mismo resultado:

Código MySQL:

mysql> SELECT T1.* FROM tabla1 T1
    -> LEFT JOIN tabla2 T2 ON T1.id = T2.id
    -> WHERE T2.id IS NULL;
+------+----------------+
| ID   | Nombre_Usuario |
+------+----------------+
|    2 | Juan           |
|    4 | Paco           |
+------+----------------+
2 rows in set (0.00 sec)
 

Observa que la condición IS NULL se debe colocar el la sección WHERE, la explicación visual se da si hacemos un SELECT * de ambas tablas:

Código MySQL:

mysql> SELECT * FROM tabla1 T1
    -> LEFT JOIN tabla2 T2 ON T1.id = T2.id
    -> ORDER BY T1.id;
+------+----------------+------+-------------+
| ID   | Nombre_Usuario | id   | descripcion |
+------+----------------+------+-------------+
|    1 | Ramon          |    1 | Ramon       |
|    2 | Juan           | NULL | NULL        |
|    3 | Hugo           |    3 | Hugo        |
|    4 | Paco           | NULL | NULL        |
|    5 | Luis           |    5 | Luis        |
+------+----------------+------+-------------+
5 rows in set (0.00 sec)
 

Observa que los campos que no tienen una correspondencia el la tabla2 se ponen en NULL, por lo tantos son sólo estos los que te interesa recuperar.

finalmente mencionas esto:

estas bases son transaccionales, por lo cual no poseen indices en sus tablas.

No entiendo qué tiene que ver una cosa con la otra... ¿a qué te refieres con que son bases transaccionales y por qué razón no poseen índices?

Saludos
Leo.

[David_Carrera_G]

Esto pasa, porque las tablas se cargan a través de procesos automáticos, los cuales sacan los datos de unos TXT y EXCEL respectivamente, según el anterior DBA estas no debían poseer indices para que las transacciones(INSERT, UPDATE, DELETE) fueran más rápidas, pero en mi automatización he modificado algunas estableciendo indices para la generación de nuevas consultas, ahora yo tengo la duda sobre los indices, ya que tengo entendido que estos pueden ser identificadores principales como PK o también puede ser otro tipo de datos.???

Gracias por tus respuestas...

[leonardo_josue]

Hola de nuevo David_Carrera_G:

Esto pasa, porque las tablas se cargan a través de procesos automáticos, los cuales sacan los datos de unos TXT y EXCEL respectivamente, según el anterior DBA estas no debían poseer indices

Vayamos por partes... usualmente, cuando haces CARGAS MASIVAS DE INFORMACION, efectivamente, no utilizas índices, para hacer más rápidas las inserciones, sin embargo, esto sólo aplica para tablas temporales. En un Modelo de BD's "Definitivo" DEBES SIEMPRE UTILIZAR INTEGRIDAD REFERENCIAL.

Uno de mis maestros en la universidad dijo alguna vez: Una Base de Datos sin integridad referencial es simplemente un montón de basura...

Cuando tienes necesidad de importar información desde archivos planos como TXT o Hojas de cálculo de EXCEL, lo más recomendable es que lo hagas a través de TABLAS TEMPORALES SIN INDICES, ya que efectivamente, esto hace que la carga de la información sea más rápida. Una vez que tienes cargada la información en las tablas temporales, entonces lo conveniente es activar los índices y entonces sí, pasar la información hacia las tablas definitivas. Insisto, el omitir los indices y llaves, debería ser sólo para hacer inserciones masivas, pero NUNCA DEBERÍA SER UNA PRÁCTICA GENERAL.

En cuanto a los índices, hay que diferenciar los campos llaves (PK, FK) a índices comunes... aunque pareciera que estamos hablando de lo mismo, no es así. Los índices pueden definirse sobre cualquier campo, INDEPENDIENTEMENTE DE SI SON LLAVES O NO... y por otro lado, una LLAVE es por defecto un tipo especial de índice. Te recomiendo que le des un vistazo a la teoría, checa en primer lugar estas dos ligas:

http://dev.mysql.com/doc/refman/5.0/...l-indexes.html
http://www.dimensis.com/consejos-1-1.html

Esto creo que te puede ser de utilizad.

Saludos
Leo.

[David_Carrera_G]

Estimado, si bien comprendo el tema de los indices, de que manera se conectan estos, a través de los INNER JOIN que estoy utilizando, o solo al agregar un indice la tabla esta mejora su timpo de respuesta. Saludos y gracias.