Consulta con indices

[xorow]

Hola, estoy testeando una consulta en mysql y veo que se demora mas de lo que debería. La consulta es la siguiente:

Código SQL:

EXPLAIN SELECT HIGH_PRIORITY main.id, main.activado, ... FROM articulo main, articulo_detalle artd WHERE main.id=artd.id_articulo AND main.id_empresa='5523' AND main.referencia='06b63fa08a306564e8ea56574...' LIMIT 1
 

La cosa está así, la tabla contiene aproximadamente un millor y medio de referencias, para agilizar la consulta he creado un indice en el campo id_empresa y otro en el campo referencia pero solo con 8 digitos para no indexar todo el campo ya que es un varchar(60).

Lo que no llego a entender bien es que al ejecutarla con explain me devuelve rows: 197491. Es decir, ha tenido que escanear 197491 lineas para encontrarla. Sin embargo, si ejecuto la siguiente sentencia:

Código MySQL:

SELECT ... 
FROM  `articulo` 
WHERE  `referencia` LIKE  '06b63fa0%'
 

Es decir, hago la consulta con los primeros 8 digitos de la referencia, para ver cuantas coincidencias pueden haber con estos y me devuelve solamente 1 línea, con lo cual no llego a entender porque en la consulta anterior ha tenido que escanear tantas lineas si realmente el indice habría tenido que dar coincidencia con esa misma.

[gnzsoloyo]

No es lo mismo esto:

Código MySQL:

SELECT ... 
FROM articulo main, articulo_detalle artd 
WHERE main.id=artd.id_articulo 
    AND main.id_empresa='5523' 
    AND main.referencia='06b63fa08a306564e8ea56574...' 
LIMIT 1
 

Que hacer

Código MySQL:

SELECT ... 
FROM articulo main
WHERE main.id_empresa='5523' 
    AND main.referencia='06b63fa08a306564e8ea56574...' 
LIMIT 1
 

No es lo mismo revisar lo que lee con una sola tabla que con un JOIN. SUpongo que no esperarás en ese contexto que lea la misma cantidad de registros.
Por otro lado, no nos estás mostrando el resultado completo del EXPLAIN, el que contiene mucha más información que simplemente la cantidad de registros accedidos.
Finalmente, los JOIN implícitos, con la condición en el WHERE son de muy mala performance. Es preferible hacer JIN explícitos, del tipo:

Código MySQL:

SELECT ... 
FROM articulo A INNER JOIN articulo_detalle D ON A.id = D.id_articulo 
    AND main.id_empresa = 5523
    AND main.referencia='06b63fa08a306564e8ea56574...' 
LIMIT 1
 

Además, como tal vez notes, no es buena práctica realizar comparaciones de campos numéricos contra valores tratados como cadenas.
Me refiero específicamente a esto:

Código MySQL:

AND main.id_empresa='5523'
 

Si id_empresa es un campo numérico no se debe poner el valor a comparar entre apóstrofes, porque eso genera una conversión implicita en MySQL que a la larga hace perder performance, y además oculta errores de variables en programación, porque por ejemplo si ese valor ingresara vacío quedaría así

Código MySQL:

AND main.id_empresa=''
 

y eso sería producto de un error de programación, pero no dispararía ningún error, como debiera suceder, por ende tendrías un defecto de programación oculto e indetectado... hasta que se produjera un fallo catastrófico.

Finalmente, indexar por 8 caracteres, cuando tienes una cadena de 60... es una muy mala idea. La cantidad de coincidencias podría ser elevadísima, y jamás lo sabrías a menos que revisases con cuidado la base.

[jurena]

Una cosa más:
imagino que tendrás indexado el campo id_articulo de articulo_detalle.

[xorow]

Muchas gracias a los 2 por responder. En primer lugar, el campo id_articulo si que está indexado, es una clave primaria, al igual que el campo id en la tabla articulo.

Por otra parte he corregido las malas prácticas en el código SQL que me has mencionado gnzsoloyo. La consulta ha quedado así:

Código MySQL:

SELECT ... FROM articulo a INNER JOIN articulo_detalle ad ON  a.id=ad.id_articulo WHERE referencia='b4f9ad5470083b2cd22a2f4ec...' AND id_empresa=886
 

Por otra parte, el campo referencia es un valor variable, ya que las referencias cada vendedor tiene la suya propia y de momento obtámos por dar un valor muy elevado al campo de 60 para posteriormente reducirlo. Pero rara vez el contenido en referencia se excede de 20 caractéres.

Por último el contenido de explain es el siguiente:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a ref PRIMARY,id_empresa id_empresa 4 const 197487 Using where
1 SIMPLE ad eq_ref PRIMARY PRIMARY 4 --- 1 Using index