Re: Slow Union Query Opimization. Need help.
My suggestion would be to evaluate the derived tables separately and see if there is a specific matching expression that is producing a slow query. In other words, execute the following and post back the results:
EXPLAIN
SELECT
p.ManProdNr
, p.ProdText as description
, p.HEK as price
, p.availabilitycode as availability
, p.ID
, p.ManText as manufacturer
, p.Quantity1 as qty
, p.ProductState as state
, p.PromoState as promotion
, p.ProductHasOptions
FROM products p
LEFT JOIN products_user_favorites pf
ON p.ManProdNr = pf.ManProdNr
AND p.ManText = pf.ManText
AND pf.userid = 'test'
WHERE 1
AND MATCH(p.ProdText,p.ProdClass,p.ProdSubClass) AGAINST ('+laser*' in boolean mode)
AND p.Distributor <> 1 \G
EXPLAIN
SELECT
p1.ManProdNr
, p1.ProdText as description
, p1.HEK as price
, p1.availabilitycode as availability
, p1.ID
, p1.ManText as manufacturer
, p1.Quantity1 as qty
, p1.ProductState as state
, p1.PromoState as promotion
, p1.ProductHasOptions
FROM products p1
LEFT JOIN products_user_favorites pf1
ON p1.ManProdNr = pf1.ManProdNr
AND p1.ManText = pf1.ManText
AND pf1.userid = 'test'
WHERE 1
AND MATCH(p1.Rev_ProdText,p1.Rev_ProdClass,p1.Rev_ProdSubClass) AGAINST ('+resal*' in boolean mode)
AND p1.Distributor <> 1 \G
CREATE TEMPORARY TABLE tmp_results
SELECT
p.ManProdNr
, p.ProdText as description
, p.HEK as price
, p.availabilitycode as availability
, p.ID
, p.ManText as manufacturer
, p.Quantity1 as qty
, p.ProductState as state
, p.PromoState as promotion
, p.ProductHasOptions
FROM products p
LEFT JOIN products_user_favorites pf
ON p.ManProdNr = pf.ManProdNr
AND p.ManText = pf.ManText
AND pf.userid = 'test'
WHERE 1
AND MATCH(p.ProdText,p.ProdClass,p.ProdSubClass) AGAINST ('+laser*' in boolean mode)
AND p.Distributor <> 1
UNION
SELECT
p1.ManProdNr
, p1.ProdText as description
, p1.HEK as price
, p1.availabilitycode as availability
, p1.ID
, p1.ManText as manufacturer
, p1.Quantity1 as qty
, p1.ProductState as state
, p1.PromoState as promotion
, p1.ProductHasOptions
FROM products p1
LEFT JOIN products_user_favorites pf1
ON p1.ManProdNr = pf1.ManProdNr
AND p1.ManText = pf1.ManText
AND pf1.userid = 'test'
WHERE 1
AND MATCH(p1.Rev_ProdText,p1.Rev_ProdClass,p1.Rev_ProdSubClass) AGAINST ('+resal*' in boolean mode)
AND p1.Distributor <> 1;
EXPLAIN
SELECT
x.ManProdNr as ManProdNr
, x.ProdText as description
, MIN(x.HEK) as price
, x.availabilitycode as availability
, x.Id
, x.ManText as manufacturer
, SUM(x.Quantity1) as qty
, MAX(x.ProductState) as state
, MAX(x.PromoState) as promotion
, MAX(x.ProductHasOptions) as ProductHasOptions
FROM tmp_results
INNER JOIN products x
ON tmp_results.ManProdNr = x.ManProdNr
AND tmp_results.Manufacturer = x.ManText
GROUP BY x.ManText, x.ManProdNr
ORDER BY x.ManText, x.ManProdNr \G
Cheers,
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com
Edited 1 time(s). Last edit at 06/29/2006 04:54PM by Jay Pipes.