asked on

Optimize this MSSQL Query

Hi ,

I have this MySQL Query, it currently is not well optimized

I believe i know what needs to be done - but i would like to get an experts take on this.

Thanks

select p.products_id, p.products_small, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price, pd.products_description

from orders_products opa, orders_products opb, orders o, products p

left join specials s on p.products_id = s.products_id, products_description pd, categories c, products_to_categories p2c

where c.categories_status='1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and opa.products_id = '0' and opa.orders_id = opb.orders_id and opb.products_id != '0' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' and pd.products_id = p.products_id and pd.products_id = p2c.products_id group by p.products_id order by o.date_purchased desc limit 6;

ASKER CERTIFIED SOLUTION

Argenti

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Terry Woods

Argent1, note that different rows are being joined beween opa and opb. See the filters:
opa.products_id = '0'
and opb.products_id != '0'

There may be good reason for this.

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

if the order by isn't needed several tables/joins could be avoided I think