Optimización de consulta

[Noonesun]

Estoy intentando reducir el tiempo (26 segundos) de la siguiente consulta:

SELECT C.name, count(*) as total
FROM packages P
LEFT JOIN `ref_package_to_category` PC ON P.Id = PC.Package_Id
INNER JOIN Categories C ON PC.Category_Id = C.Id
INNER JOIN
(SELECT VTP.VulnPack_Id FROM ref_vuln_to_vulnpack VTP
INNER JOIN vulnerability V ON VTP.VulnPack_Id = V.Id
WHERE PublishedDateTime > 2008-01-01) as VP
ON VP.VulnPack_Id = P.Id
GROUP BY C.Id

Para ello quería reducir el número de columnas de la tabla packages, que tiene 9 columnas de formato varchar(100) y de la cual sólo necesito el índice. He escrito la siguiente consulta:

SELECT PAK.name, count(*) as total
FROM
(SELECT P.Id as PaqID, C.name, C.Id as catID
FROM packages P
LEFT JOIN `ref_package_to_category` PC ON P.Id = PC.Package_Id
INNER JOIN Categories C ON PC.Category_Id = C.Id ) as PAK
INNER JOIN
(SELECT VTP.VulnPack_Id FROM ref_vuln_to_vulnpack VTP
INNER JOIN vulnerability V ON VTP.VulnPack_Id = V.Id
WHERE PublishedDateTime > 2008-01-01) as VP
ON VP.VulnPack_Id = PaqID
GROUP BY catIDs

pero cada vez que la ejecuto el proceso de mysql ocupa al 100% la cpu y después de un rato tengo que pararlo manualmente.

¿Hay algún error que no esté viendo?
Gracias

[quimfv]

Código:

SELECT C.Id, C.name, count(*) as total
FROM (packages P 
           LEFT JOIN (`ref_package_to_category` PC 
                               INNER JOIN Categories C 
                               ON PC.Category_Id = C.Id )
          ON P.Id = PC.Package_Id )
          INNER JOIN 
               (SELECT VTP.VulnPack_Id 
                  FROM ref_vuln_to_vulnpack VTP 
                  INNER JOIN vulnerability V 
                  ON VTP.VulnPack_Id = V.Id
                  WHERE PublishedDateTime > 2008-01-01) as VP
ON VP.VulnPack_Id = P.Id
GROUP BY C.Id, C.name

PublishedDateTime de que tabla viene?

Código:

SELECT C.Id, C.name, count(*) as total
FROM (packages P 
             LEFT JOIN 
                   (`ref_package_to_category` PC 
                      INNER JOIN Categories C 
                      ON PC.Category_Id = C.Id )
             ON P.Id = PC.Package_Id )
WHERE P.Id in  (SELECT VTP.VulnPack_Id 
                             FROM ref_vuln_to_vulnpack VTP 
                                  INNER JOIN vulnerability V 
                                  ON VTP.VulnPack_Id = V.Id
                             WHERE PublishedDateTime > 2008-01-01) 
GROUP BY C.Id, C.name

No se ... nos das poca info.... por que el LEFT JOIN? hay pakages sin categoria??...

Quim

[Noonesun]

PublishedDateTime viene de la tabla vulnerability.
Básicamente la única diferencia que hay entre las dos consultas está en la forma de acceder a package. He usado LEFT JOIN porque no me han asegurado que todos los paquetes vayan a tener una categoría asociada.

Código:

SELECT PAK.name, count(*) as total
FROM 
	(SELECT P.Id as PaqID, C.name, C.Id as catID
	FROM packages P 
	LEFT JOIN `ref_package_to_category` PC ON P.Id = PC.Package_Id 
	INNER JOIN Categories C ON PC.Category_Id = C.Id ) as PAK
INNER JOIN  		
	(SELECT VTP.VulnPack_Id FROM ref_vuln_to_vulnpack VTP 
	 INNER JOIN vulnerability V ON VTP.VulnPack_Id = V.Id 
	 WHERE PublishedDateTime > 2008-01-01) as VP
ON VP.VulnPack_Id = PaqID
GROUP BY catID