Ayuda para Optimizar consulta MySQL

[claus0618]

Hola amigos...Tengo la siguiente consulta:

select internalID, ProdTitle from product where
prodActive = 1 and
internalID not in (select DISTINCT(ProdMainID) FROM product_categories_members)

Esta consulta me permite obtener los productos que aun no tienen una categoria asignada. Sin embargo la tabla product tiene 2500 registros y la tabla product_categories_members tiene 8000. utilizando esta consulta me da como resultado 2500*8000 = 20'000.000 de registros totales a consultar. Es muy grande y por eso se cae el servidor.

¿Alguien podria ayudarme a mejorar esta consulta? he intentado con Joins y tampoco, el servidor termina por caerse. Les agradezco mucho.

[gnzsoloyo]

Probemos esto:

Código MySQL:

SELECT 
    internalID, 
    ProdTitle 
FROM 
    product P LEFT JOIN product_categories_members PCM ON P.internalID = PCM.ProdMainID
WHERE 
    PCM.ProdMainID IS NULL 
    AND P.prodActive = 1;
 

[claus0618]

Muchas gracias por tu aporte gnzsoloyo. Corri esta consulta localmente y se cae el apache. Y la corri online, me trajo resultados pero se demora casi 1 minuto en terminar solo de consultar. Es mucho tiempo aun, porque despues de esto debe armar un Excel con los resultados. ¿Alguna otra idea?

:)

[gnzsoloyo]

Otra posibilidad:

Código MySQL:

SELECT
    P.internalID,
    P.ProdTitle
FROM
    (SELECT internalID, ProdTitleproduct FROM product WHERE prodActive = 1) P
    LEFT JOIN
    product_categories_members PCM ON P.internalID = PCM.ProdMainID
WHERE
    PCM.ProdMainID IS NULL;
 

Habría que probar también establecer un índice en la tabla `product_categories_members` sobre el campo ProdMainID, si es que no existe ya.
El tema pasa aquí por lograr definir un modo de alta selectividad en una de las dos tablas.
Si una tiene 25.000 y la otra 8.000l oque hay que lograr es reducir primero los registros devueltos por la primera, y luego los de la segunda.
El tema, en una página Web, es que la lentitud no está dada tanto por los registros, sino por la pasa de datos transportada, lo que significa que lo que se satura no es MySQL sino el TCP...

[claus0618]

Muchas gracias gnzsoloyo por tu nuevo aporte. Esta se demoro mas o menos lo mismo que las otras de 65 - 70 segundos en traer los resultados.

Tengo una nueva opcion.

Código MySQL:

select internalID, ProdTitle from product where
prodActive = 1 and
 not exists (select DISTINCT(ProdMainID) FROM product_categories_members where internalID = ProdMainID)
 

Esta consulta se demora 35 segundos en traer los datos. Aun sigue siendo mucho tiempo para un servidor web, pero por ahora es la mejor.

De nuevo muchas gracias.

[elhazedor]

Claus permíteme algunos pequeños consejos, sería de gran ayuda que luego nos contaras como te ha ido.
-Primer consejo:En teoria mySql optimiza las subselects, es decir, si no cambia dicha sentencia utiliza los datos extraidos la primera vez. De esto podriamos deducir que la subselect de la primera opción que diste es "la más" correcta (sólo en teoria).
-Segundo consejo, para la tabla product crea un indice para InternalId(es posible que se cree implicitamente)
-Por último, si necesitas velocidad, utiliza el Engine=MYISAM y sacrifica las ventajas de INNODB O BDB, MYISAM es casi el triple de rápido

Un saludo, se agradeceria respuesta con los resultados