MySQL - Consulta MUY lenta a la tabla information_schema.referential_constraints

Buenas tardes

Tengo esta consulta a la tablas del sistema para obtener la estructura de las claves foráneas y actualizar la base de datos de los clientes:

SELECT Refer.constraint_name, Refer.unique_constraint_name, Refer.update_rule, Refer.delete_rule, Refer.table_name, Refer.referenced_table_name, Claves.ordinal_position, Claves.column_name FROM information_schema.referential_constraints AS Refer, information_schema.key_column_usage AS Claves WHERE Refer.constraint_schema = "MI_ESQUEMA" AND (Claves.constraint_schema, Claves.table_name, Claves.constraint_name) = ("MI_ESQUEMA", Refer.table_name, Refer.constraint_name) ORDER BY Refer.table_name, Refer.referenced_table_name, Refer.constraint_name, Claves.ordinal_position

El problema es que en ocasiones tarda demasiado, a veces hasta tres minutos sólo en devolver los resultados.

Utilizo la versión de MySQL 5.7.18

Se admiten sugerencias. Gracias

Hola elhabraque.
Por lo general cuando una consulta se vuelve lenta es la falta de índices en campos que se utilizan como condición en el where.
Además ahí estás consultando 2 bbdd distintas que quizás no se están relacionando correctamente con sus campos.
El resultado que devuelve es correcto??
Si es así podrías jugar con los índices pero con cuidado en la tabla de la dase del sistema, y por supuesto en la tuya.
Espero que te sea útil la información.

Hola elhabraque:

Algunas recomendaciones para mejorar el rendimiento de la consulta:

1. No hagas JOIN's implícitos, sino que utiliza JOIN's explícitos, en otras palabras, en lugar de hacer esto:


Haz esto:


Los JOIN's (INNER, LEFT O RIGTH) tienen mejor rendimiento que listar todas tus tablas en el FROM y hacer las uniones en el WHERE.

2. De acuerdo a mis pruebas, es más rápido utilizar consultas tipo AND que hacer las comparaciones como las estás haciendo, es decir en lugar de hacer esto:


es mejor hacer esto:


3. Si te es posible no utilices la cláusula ORDER BY. La ordenación es un proceso que puede matar el performance... si es absolutamente necesario que hagas una ordenación, entonces es recomendable que utilices un INDICE con todos los campos que van a intervenir en esa ordenación,

entonces, la consulta podría quedar más o menos así:


haz la prueba y nos comentas.

Saludos
Leo.

Muchas gracias, lo probaré.

Se me había pasado ajustar lo del INNER JOIN ya que normalmente en el resto de consultas suelo usar esta unión; de hecho, en el resto de consultas utilizo LEFT JOIN para saber qué índices están en la base de datos del cliente y cuáles envío con la versión.

Por lo que respecta a las cláusulas AND o utilizar las comparaciones como la indiqué, resulta que las pruebas que he hecho me han llevado a utilizar mayoritariamente esta sintaxis, porque además en varios foros he leído que para aumentar la velocidad es mejor reducir los AND. Otra cosa es que al tratarse de tablas del sistema, no tienen índices por lo que no se puede acelerar por ese lado (al parecer la versión 8.0 ya los tienen, así que estaré atento al asunto).

Finalmente el ORDER BY es necesario, ya que tengo que obtener el resultado con el orden exacto de los campos que forman el índice, para analizar si ha cambiado alguna condición del índice respecto de la nueva versión (por ejemplo, si intercambio el orden de dos campos). Por eso asumo esa ralentización.

Hola de nuevo elhabraque:

Sería interesante con nos compartieras las referencias a los foros donde se menciona que las comparaciones son mejores como las haces y no con AND's... esta funcionalidad la probé hace mucho tiempo y tuve resultados distintos, por eso dejé de usarla, pero insisto, puede que tengas razón y sea mejor hacerlo así, siempre es tiempo de cambiar.

En cuanto a lo de los índices, pues si, al ser tablas de sistema no hay mucho que hacer, pero bueno, qué se la va a hacer...

Saludos!
Leo.

Buenas

Ahora no encuentro dónde lo leí, pero adjunto algunos enlaces:

https://www.adictosaltrabajo.com/tutoriales/optimizacion-de-consultas-en-mysql/
https://www.sortea2.com/blog/2010/06/10-consejos-optimizar-mysql/

Cuando cambié la forma de hacer las consultas, hice muchas pruebas con consultas complejas y miles de registros, y después de asegurarme que el servidor no almacenara la consulta en caché encontré diferencias bastante sensibles.

Ahora bien, esto lo he probado recientemente con las versiones 5.6 y posteriores, que es cuando comencé con MySQL.

De hecho, en el programa donde estaba la consulta, ésta era la única que quedaba con ese formato.

Un saludo