Help me speed up my sql query

[subzerostudio]

Hi

 

I have an SQL statement. What it does is look through all products for a specific manufacturer on an E-Commerce site, looking for a match of two things: size and colour. The query is given below:

 

SELECT <list of selected files here>
FROM <list of tables where>
WHERE p.products_status = '1' 
AND   pd.language_id = '1' 
AND   fp.code = 'GBP' 
AND   m.manufacturers_id = '1' 
AND   pa.options_values_id in (108,696,698,705,709,710,716,713,731,734,735,755,756) 
AND   p.products_id IN 
      (SELECT products_id FROM products_attributes WHERE options_id=2 AND options_values_id=236)
ORDER BY pd.products_name

 

So the colour is matched by passing the query a list of colour ID's.. Using this line here:

AND   pa.options_values_id in (108,696,698,705,709,710,716,713,731,734,735,755,756) 

 

However the size is also stored in the same table as the colour (with a different options id) and this is leading to problems. The following line:

AND   p.products_id IN 
      (SELECT products_id FROM products_attributes WHERE options_id=2 AND options_values_id=236)

Is taking the query from a 0-1sec query, to a 30 sec query.. and thus bogging down the server very noticably.

 

Is there a way that it can be rewritten without altering the database structure?

 

Any help appreciated 

 

Mike

[Wildbug]

Instead of using a subquery, have you tried writing it as a JOIN?

 

FROM <current table list> JOIN products_attributes ON p.products_id=products_attributes.products_id AND products_attributes.options_id=2 AND products_attributes.options_values_id=236

// or:

FROM <current table list>, products_attributes
WHERE
p.products_id=products_attributes.products_id
AND products_attributes.options_id=2
AND products_attributes.options_values_id=236

[cmgmyr]

Yes, JOINs should speed this up a lot more.