Re: Slow Union Query Opimization. Need help.
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