MySQL - Consulta MUY lenta a la tabla information_schema.referential_constraints

   
Vista:

Consulta MUY lenta a la tabla information_schema.referential_constraints

Publicado por elhabraque (12 intervenciones) el 24/07/2017 22:06:35
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
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder

Consulta MUY lenta a la tabla information_schema.referential_constraints

Publicado por Nicolas (40 intervenciones) el 24/07/2017 23:15:27
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.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Consulta MUY lenta a la tabla information_schema.referential_constraints

Publicado por leonardo_josue (388 intervenciones) el 25/07/2017 16:15:10
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:

1
2
3
...
FROM tabla1, tabla2 WHERE tabla1.campo = tabla2.campo
...

Haz esto:

1
2
3
4
...
FROM tabla1
INNER JOIN tabla2 ON tabla1.campo = tabla2.campo
...

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:

1
2
3
...
WHERE (tabla1.campo1, tabla1.campo2) = (tabla2.campo1, tabla2.campo2)
...

es mejor hacer esto:

1
2
3
4
5
...
WHERE
  tabla1.campo1 = tabla2.campo1
  AND tabla1.campo2 = tabla2.campo2
...

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í:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 Refer,
INNER JOIN
  information_schema.key_column_usage Claves
ON
    Claves.constraint_schema = Refer.constraint_schema  AND
    Claves.table_name = Refer.table_name AND
    Claves.constraint_name = Refer.constraint_name
WHERE
Refer.constraint_schema = "MI_ESQUEMA"

haz la prueba y nos comentas.

Saludos
Leo.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar

Consulta MUY lenta a la tabla information_schema.referential_constraints

Publicado por elhabraque (12 intervenciones) el 26/07/2017 01:07:41
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.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Consulta MUY lenta a la tabla information_schema.referential_constraints

Publicado por leonardo_josue (388 intervenciones) el 26/07/2017 19:34:35
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.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Consulta MUY lenta a la tabla information_schema.referential_constraints

Publicado por elhabraque (12 intervenciones) el 27/07/2017 02:04:47
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
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Revisar política de publicidad