Slow Union Query Opimization. Need help.

[Roland Meier]

Good morning

I have a complicated union query using fulltext searches. That query is working fine but becomes terribly slow when more than 5000 rows are returned:

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
(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 )
as result, products x WHERE result.ManProdNr = x.ManProdNr and result.Manufacturer = x.ManText GROUP BY manufacturer, ManProdNr order by Manufacturer, ManProdNr

My Explain Plan says:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 34067 Using temporary; Using filesort
1 PRIMARY x ref ManProdNr,ManText,ManText_2,ManProdNr_2 ManProdNr 50 result.ManProdNr 2 Using where
2 DERIVED p fulltext Distributor,ProdText ProdText 0 1 Using where
2 DERIVED pf ref userid,ManText,ManProdNr ManProdNr 50 catalogintegra2.p.ManProdNr 1 Using where
3 UNION p1 fulltext Distributor,Rev_ProdText Rev_ProdText 0 1 Using where
3 UNION pf1 ref userid,ManText,ManProdNr ManProdNr 50 catalogintegra2.p1.ManProdNr 1 Using where
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL

Can anyone give me a hint where the query could be optimized to work faster for huge selects.

Thanks and regards,

Roland