MySQL Forums
Forum List  »  Optimizer & Parser

Re: Slow Union Query Opimization. Need help.
Posted by: Jay Pipes
Date: June 29, 2006 04:53PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Slow Union Query Opimization. Need help.
2566
June 29, 2006 04:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.