Duda con EXPLAIN en muchos registros

[SUSMO]

Hola a todos,

En mi pàgina web tengo consultas que con muchos registros me peta por superar el tiempo de ejecución.
Se que esto puedo arreglarlo augmentando el memory_limit de php pero es chapucilla y prefiero optimizar las consultas.

Mirando por inet he visto que existe EXPLAIN para ver como actúan las consultas y aquí viene mi duda.

Tengo la siguiente consulta:

Código MySQL:

EXPLAIN
SELECT typex.*, listM.prtc__col_name as colM, listM.value as valueM, langs.prtc__col_name AS '__colName__',
langs.malg__code AS '__code__',langs.text AS '__text__'
FROM table1 AS typex
LEFT JOIN table2 AS langs ON langs.prtx__ref = typex.ref AND langs.prt__name = 'gafas'
LEFT JOIN table3 AS listM ON listM.prtx__ref = typex.ref AND listM.prt__name = 'gafas'
WHERE typex.ref in ('0010021','0010022','0010023')
 

Con pocos registros, no hay problema y lo que me devuelve es:

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 'typex', 'range', 'PRIMARY', 'PRIMARY', '302', NULL, 3, 'Using where'
1, 'SIMPLE', 'langs', 'ref', 'PRIMARY,Index_3', 'Index_3', '152', 'bassol.typex.ref', 9, ''
1, 'SIMPLE', 'listM', 'ref', 'PRIMARY,Index_2', 'Index_2', '152', 'bassol.typex.ref', 6, ''

Pero con muuuuuuchos registros me devuelve:

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 'typex', 'ALL', 'PRIMARY', NULL, NULL, NULL, 2735, 'Using where'
1, 'SIMPLE', 'langs', 'ref', 'PRIMARY,Index_3', 'Index_3', '152', 'bassol.typex.ref', 9, ''
1, 'SIMPLE', 'listM', 'ref', 'PRIMARY,Index_2', 'Index_2', '152', 'bassol.typex.ref', 6, ''

El type me cambia de range a ALL. Mirando el manual de referencia de MySQL, si sale type = "ALL" es muy mala opción, ya que hace que la consulta sea más lenta y suele ser por no tener definido un índice en la tabla.
Esto no es cierto puesto que la clave primaria de table1 es ref, las claves primarias de table2 son prtx__ref,prtc__col_name,prt__name,malg__code donde table1.ref tiene que corresponder a table2.prtx__ref y las claves primarias de table3 son: prtx__ref, prtc__col_name, prt__name, value donde table1.ref tiene que corresponder a table3.prtx__ref.

No entiendo pq me sale type = ALL con muchos registros y con pocos me sale range si en las dos consultas está definidas las claves por igual.
Tampoco se como solucionarlo para optimizar mi consulta.

¿Alguien puede ayudarme?

[gnzsoloyo]

Normalmente los planificadores de consultas toman la decisión de eliminar los índices en la salida cuando el índice o la condición del JOIN no tiene la suficiente selectividad como para descartar un porcentaje importante de registros en el matching. Es posible que esta sea la razón de por qué, en un conjunto mayor, donde se requiere leer una gran cantidad de bloques de datos, el porcentaje de registros a revisar termine siendo igual o mayor al 50% de lo leído, mientras que en el conjunto menor la cantidad de registros permite que sólo se use un bloque de lectura y por tanto el índice sea ventajoso.
Como regla general (que se enseña en las carreras de informática), si un conjunto dado de datos devueltos implica el 50% de la tabla, el tiempo de lectura termina siendo igual que leer toda la tabla (sólo le faltaría un salto más de lectura), por lo que el siguiente algoritmo a ejecutar sería el mismo que un full scan. Entonces simplemente se decarta el ídice porque el overhead de leerlo se compensa con las lecturas que no se realizan en él. Directamente se pasa a la tabla.
Habría que analizar una a una las secciones de datos que se leen, para ver si afinando la lectura, puede reducirse el bloque a leer. Una idea sería transformar las tablas en tablas derivadas, y a su vez definirles indices en los campos que se requieren conocer (table2.prt__name Y table3.prt__name):

Código MySQL:

EXPLAIN
SELECT 
    typex.*, 
    listM.prtc__col_name colM, 
    listM.value valueM, 
    langs.prtc__col_name '__colName__',
    langs.malg__code '__code__',
    langs.`TEXT` '__text__'
FROM 
    table1 typex
    LEFT JOIN 
    (SELECT * FROM table2 WHERE prt__name = 'gafas') langs ON langs.prtx__ref = typex.ref 
    LEFT JOIN 
    (SELECT * FROM table3 WHERE prt__name = 'gafas') listM ON listM.prtx__ref = typex.ref 
WHERE 
    typex.ref IN ('0010021','0010022','0010023')
 

[SUSMO]

Hola gnzsoloyo,

Primero de todo agradecerte tu explicación.

Efectivamente los registros que me devuelve la consulta puede retornar más del 50% del total de registros de la tabla.
Yo tenía entendido que hacer selects anidados ralentizaba la ejecución de la consulta y al probarla como la has puesto tú he visto que efectivamente se ha vuelt muuucho más lenta de como ya la tenía puesta en un principio.

¿Hay alguna otra opción para optimizarla?

[quimfv]

Creo que la idea de gnzsoloyo no es usar la consulta tal y como te la manda si no analizar el explain para ver que pasa en esas tablas que ahora son derivadas...

Y digo creo si no es así gnzsoloyo seguro que te lo aclara.

No nos explicas la semantica de tus datos con lo que es mas dificil de ver posibles optimizaciones....

Por ejemplo si todos los registros de tipex tienen almenos un lang puedes cambiar left por inner y seguro que ganas, pero si existe la posibilidad que un tipex no tenga lang no sirve. Claro que eso solo lo sabes tu.

[gnzsoloyo]

Mas o menos esa era la idea. Puse un ejemplo de cómo se construye una tabla derivada buscando condiciones que tengan más selectividad.
Bajo ciertas condiciones, una subconsulta no es necesariamente más lenta, porque además la subconsulta en cuestión no va en el WHERE ni en el SELECT, que es donde realmente bajan la performance., sino en el FROM.
Si esa subconsulta devolviese menos del 20% de los registros, podría ser una forma de resolver parte del problema. Obviamente no es así, así que lo primero que hay que determinar es qué condiciones se pueden agregar para que el conjunto de datos del FROM fuesen menos (mayor selectividad).

[SUSMO]

Hola,

Por un lado, he creado dos indices más en table2 y table3, que corresponden a los campos prtx__ref y prt__name a ver si eso ayuda, pero sigue siendo muy lenta.

También he estado probando diferentes combinaciones de subconsultas y esta es la que me da mejor resultado:

Código MySQL:

SELECT typex.*, listM.prtc__col_name as colM, listM.value as valueM, langs.prtc__col_name AS '__colName__',
langs.malg__code AS '__code__',langs.TEXT AS '_text_'
FROM (
    SELECT *
    FROM table1 WHERE ref IN ('0010021','0010022','0010023')
) as typex
INNER JOIN table2 AS langs ON langs.prtx__ref = typex.ref AND langs.prt__name = 'gafas'
LEFT JOIN table3 AS listM ON listM.prtx__ref = typex.ref AND listM.prt__name = 'gafas'
 

Ejecutando la consulta que tenía al principio en el MySQL Query Browser el tiempo de ejecución que me pone es de entre 14-16 segundos y con la modificación que he hecho es entre 8-9 segundos, una gran mejora sin duda (aunque no la suficiente)

Pero en cambio al llamar a la función php que ejecuta esa consulta, el tiempo con la primera consulta era de 18 segundos y con la que acabo de poner me peta por tiempo de ejecución.
La verdad es que no le veo el sentido.

Por otro lado, el número total de registros de table1 es de 2735 y en la consulta que me da problemas todas estas las referencias estan en el "where in". Por cada registro de esta tabla hay 9 registros de table2 (campos con idioma), y en la table3 puede o no corresponder con 6 registros, lo que hace un total de 154084 registros.

La verdad es que ya no se que más probar, estoy planteandome hacer las 3 consultas por separado pero creo que eso en lugar de mejorar el tiempo de ejucción lo empeoraria ¿no?

He probado también cambiar el tipo de dato de los indices , puesto que lo tenía en VARCHAR y he leído que con CHAR es son más optimas las consultas pero he reducido sólo 1 segundo