MySQL Forums
Forum List  »  Optimizer & Parser

Re: Slow Union Query Opimization. Need help.
Posted by: Roland Meier
Date: June 30, 2006 02:30AM

Hello Anthony, hello Jay

I really appreciate your help on this matter.

Thanks for the hint of the group by clause. I forgot to include all non aggregated columns. Unfortunately this did not help me out.

This statement is used for a search on the products table and I agree that a normal user should not search on words which return huge amounts of data, but I cannot exclude it as the resultset may be filtered again afterwards.

Thank you Jay for your approach, which is very helpful. I will post the explain plans below. With this method i found out, that there is no problem with the selects of the derived table. The killer is the last statement which joins products with tmp_results. As soon as there is a group by clause, a file sort will be executed. No matter how many rows are affected. My last test was to run the last select on a table which only contains 3 rows, but still a file sort.

Please excuse my bad english, but I hope you will understand my statements. Please find the explain plans:

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

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p fulltext Distributor,ProdText ProdText 0 1 Using where
1 SIMPLE pf ref userid,ManText,ManProdNr userid 255 const 1 Using where

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

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p1 fulltext Distributor,Rev_ProdText Rev_ProdText 0 1 Using where
1 SIMPLE pf1 ref userid,ManText,ManProdNr userid 255 const 1 Using where

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


id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tmp_results ALL NULL NULL NULL NULL 4304 Using temporary; Using filesort
1 SIMPLE x ref ManProdNr,ManText,ManProdNr_2 ManProdNr 50 tmp_results.ManProdNr 2 Using where


When i delete the group by and order by statement the statement is rather fast.


Regards

Roland

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Slow Union Query Opimization. Need help.
2930
June 30, 2006 02:30AM


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.