CreativeMind CreativeMind - 4 years ago 83
SQL Question

Combine 2 MySQL queries to improve performance

I have the following 2 queries.
The first is finding some

product_id
s based on some joins. It's running fast.

SELECT Group_concat(DISTINCT( p.products_id )) AS comma_separated
FROM products p
left join specials s
ON p.products_id = s.products_id
left join products_to_categories p2c
ON p.products_id = p2c.products_id
left join products_description pd
ON p.products_id = pd.products_id
inner join products_attributes pa
ON p.products_id = pa.products_id
WHERE p.products_status = '1'
AND Date_sub(Curdate(), interval 7000 day) <= p.products_date_added
ORDER BY p.products_id DESC


After that, the following query is executed and use all the
products_id
s found in the first query:

SELECT DISTINCT pov.products_options_values_id,
pov.products_options_values_name,
pa.options_values_price,
pa.price_prefix
FROM products_attributes pa,
products_options_values pov
WHERE Find_in_set(pa.products_id,
'**Long list of comma separated products_ids found in the first query**')
AND pa.options_id = 1
AND pa.options_values_id = pov.products_options_values_id
AND pov.language_id = '1'
GROUP BY pov.products_options_values_id
ORDER BY pov.products_options_values_sort_order ASC


The list of
products_id
s in the
Find_in_set
clause is very long and making this query take quite a long time to execute.

Is there any way of using joins or other way to rewrite the second query or combine both to improve performance?

Answer Source

Try something like this

SELECT DISTINCT pov.products_options_values_id, 
                pov.products_options_values_name, 
                pa.options_values_price, 
                pa.price_prefix 
FROM   products_attributes pa, 
       products_options_values pov,
       (SELECT p.products_id
            FROM   products p 
                   left join specials s 
                          ON p.products_id = s.products_id 
                   left join products_to_categories p2c 
                          ON p.products_id = p2c.products_id 
                   left join products_description pd 
                          ON p.products_id = pd.products_id 
                   inner join products_attributes pa 
                           ON p.products_id = pa.products_id 
            WHERE  p.products_status = '1' 
                   AND Date_sub(Curdate(), interval 7000 day) <= p.products_date_added 
            GROUP BY p.products_id
        ) t
WHERE t.products_id = pa.products_id
AND pa.options_id = 1 
AND pa.options_values_id = pov.products_options_values_id 
AND pov.language_id = '1' 
GROUP  BY pov.products_options_values_id 
ORDER  BY pov.products_options_values_sort_order ASC
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download