Optimizar consulta con explain

[SUSMO]

Hola a tod@s,

Estoy intentando optimizar mis consultas a bdd, para ello he leído que una buena forma de saber que hacer es utilizando el Explain, lo he utilizado pero no se donde tengo el fallo.

Código MySQL:

EXPLAIN SELECT listM.prtx__ref, listM.value
FROM prtx_list_m AS listM
INNER JOIN prtx__comentssubs AS typex  ON typex.ref = listM.prtx__ref AND typex.public = 1
INNER JOIN prtx_prt_prc AS cat ON cat.prtx__ref = typex.ref AND cat.prt__name = 'comentssubs'
AND cat.prc__id in ('680','533','3','20')
WHERE listM.prt__name = 'comentssubs' AND listM.prtc__col_name = 'llmult'
GROUP BY ref, value
 

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE typex ALL PRIMARY NULL NULL NULL 3 Using where; Using temporary; Using filesort
1 SIMPLE cat ref PRIMARY PRIMARY 152 typex.ref 1 Using where; Using index
1 SIMPLE listM ref PRIMARY,Index_2 Index_2 214 typex.ref,const 6 Using where

Por lo que he leído, es como si no tubiera un índice asociado a la tabla prtx__comentssubs, pero eso no es cierto ya que el campo ref es la primary key de mi tabla. Por otro lado la tabla prtx_list_m tiene como clave primaria compuesta: prtx__ref, prtc__col_name, prt__name, value y como índice: prtx__ref, prt__name. La tabla prtx_prt_prc tiene como primary key compuesta: prtx__ref, prc__id, prt__name.

Alguien podria decirme como podría optimizar esta consulta? estoy desesperada, he provado diferentes combinaciones de la query y todas me devuelven lo mismo en el explain.

Aprovecho para hacer otra pregunta, que es más rápido: cat.prc__id in ('680','533','3','20') o cat.prc__id = '680' or cat.prc__id ='533' or cat.prc__id ='3' or cat.prc__id ='20' ?

[ComprasDesdeCasa]

Hola,

una consulta: los siguientes campos están indexados?
typex.ref
listM.prtx__ref
typex.public
cat.prtx__ref
typex.ref
cat.prt__name
cat.prc__id
listM.prt__name
listM.prtc__col_name

Saludos.

[SUSMO]

Hola,

Primero de todo agradecerte tu respuesta.

Los campos que me indicas son primary keys de las tablas por lo que también hacen de índices.

"Por lo que he leído, es como si no tubiera un índice asociado a la tabla prtx__comentssubs, pero eso no es cierto ya que el campo ref es la primary key de mi tabla. Por otro lado la tabla prtx_list_m tiene como clave primaria compuesta: prtx__ref, prtc__col_name, prt__name, value y como índice: prtx__ref, prt__name. La tabla prtx_prt_prc tiene como primary key compuesta: prtx__ref, prc__id, prt__name. "

El único campo que no está indexado es el typex.public pero no creo que eso haga que la consulta no sea óptima, no?

[gnzsoloyo]

Hay cosas que no me parecen correctas de tu consulta, por ejemplo:
- Pones condiciones en el FROM que deberían ir en el WHERE.
- Ciertas condiciones implican relaciones que no estás declarando en el FROM
- La existencia de esos valores en ambas tablas, no siendo PKs, hacen inferir que hay un diseño algo defectuoso (redundancia peligrosa) e indices faltantes.
- Hay campos evidentemente numéricos que se están comparando como cadenas, lo que exige una conversión implícita innecesaria (probable defecto de programación).

A mi entender, habría que replantear la consulta con algo así como:

Código MySQL:

SELECT 
    listM.prtx__ref, 
    listM.value
FROM 
    prtx_list_m listM
    INNER JOIN prtx_prt_prc  cat ON (cat.prtx__ref = typex.ref AND listM.prt__name = cat.prt__name)
    INNER JOIN prtx__comentssubs typex  ON typex.ref = listM.prtx__ref 
WHERE 
    listM.prt__name = 'comentssubs' 
    AND listM.prtc__col_name = 'llmult' 
    AND cat.prc__id IN (680, 533, 3, 20)
    AND typex.public = 1
GROUP BY ref, value
 

En ese caso por ejemplo, puede inferirse que se requieren al menos cuatro indices para que el tema funcione, sobre los campos:
- prtx_list_m.prt__name
- prtx_list_m.prtc__col_name
- prtx_prt_prc .prc__id
- prtx__comentssubs.public

También es posible que la cosa mejora si en la tabla prtx_list_m se define un índice INDEX sobre (prtx__ref, value)

Por cierto, te recomiendo enfáticamente no usar palabras reservadas como nombres de columnas, porque eso puede trae consecuencias imprevisibles (por ejemplo, puede producir errores de sintaxis indetectables). Me refiero en especial a ese campo llamado "value".

[SUSMO]

Hola gnzsoloyo,

Muchísimas gracias por tu respuesta, a raíz de ella me han surgido algunas dudas, si me las puedes responder....

Me comentas de poner las condiciones en el from, las pongo en el inner pq tengo entendido que cuantos menos registros devuelva la tabla del inner más rápida va la consulta y al poner más condiciones en el inner menos registros devuelve y por tanto menos registros a cruzar con las otras tablas del from, estoy equivocada con mi planteamiento?

Los campos que me comentas que deberian ser índices forman parte de las claves primarias de esas tablas, te refieres que al haber una condición "externa" como por ejemplo "listM.prt__name = 'comentssubs' " deberia además de ser parte de una clave primaria crear un índice sólo de ese campo? no es suficiente con que sea primary key?

Veo que en tu consulta has cambiado el orden de los inners a como yo los había puesto inicialmente: INNER JOIN prtx_prt_prc y INNER JOIN prtx__comentssubs, por algún motivo en especial? influye en la ejecución de la query el orden de los joins?

Gracias por la observación de los números puestos entre ', no pensaba que pudiera interferir en el rendimiento, lo ponia así para asegurarme de que si por alguna razón en lugar de ser número fuera string no me petara la consulta, más que nada para evitar tener que comprovar si el valor es numérico para ponerlo en la condición. Lo tendré en cuenta. Gracias también por lo de las palabras reservadas, sinceramente, no había pensado en ello