DAVID3733 Posted March 20, 2011 Share Posted March 20, 2011 Hi There Spent until the early hours trying to optimise my site further and now must get some sleep, I have a query in products_info.php which is being drawn in from somewhere but i am sure i have looked in every file that is brought in, anyone know where it is Query: select pd.products_name, p.products_model, p.products_image, p.products_id, p.manufacturers_id, p.products_price, p.products_weight, p.products_qty_blocks, p.products_tax_class_id, p.distributors_id, IF(s.status = '1' AND s.stores_id = '1', s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = '1' AND s.stores_id = '1', s.specials_new_products_price, p.products_price) as final_price from products_to_categories p2c, ((products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_description pd) left join specials s on p.products_id = s.products_id and s.stores_id = '1') INNER JOIN products_to_stores p2s ON p.products_id = p2s.products_id where p2s.stores_id = '1' AND p.products_status = '1' and p.products_id = '13070' and pd.products_id = '13070' and pd.language_id = '1' it takes the longest on the page to load and i want to try and improve on it so any tips on rearranging the query would also be welsomed Thank you David David Link to comment Share on other sites More sharing options...
DAVID3733 Posted March 21, 2011 Author Share Posted March 21, 2011 Hi There I believe this query is broken down in products_info.php select pd.products_name, p.products_model, p.products_image, p.products_id, p.manufacturers_id, p.products_price, p.products_weight, p.products_qty_blocks, p.products_tax_class_id, p.distributors_id, IF(s.status = '1' AND s.stores_id = '1', s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = '1' AND s.stores_id = '1', s.specials_new_products_price, p.products_price) as final_price from products_to_categories p2c, ((products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_description pd) left join specials s on p.products_id = s.products_id and s.stores_id = '1') INNER JOIN products_to_stores p2s ON p.products_id = p2s.products_id where p2s.stores_id = '1' AND p.products_status = '1' and p.products_id = '35390' and pd.products_id = '35390' and pd.language_id = '1' if i put this direct into PHPmY admin it dispays what is should but repeats it 36675 times which is the number of products in the DB, is this right, should it display the same data over and over, is this causeing me a lag in the query, or perhaps its supposed to do this i dont know that is why i am asking for someone to look at it with a bit more knowledge than my own. I have tried to create Indexs and here is my explain table id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE p const PRIMARY,IDX_products_id,IDX_products_status PRIMARY 4 const 1 1 SIMPLE m const PRIMARY,IDX_manufacturers_id PRIMARY 4 const 1 Using index 1 SIMPLE pd const PRIMARY,language_id,products_id PRIMARY 8 const,const 1 1 SIMPLE s ref idx_specials_products_id,IDX_stores_id idx_specials_products_id 4 const 1 1 SIMPLE p2s ref products_id,stores_id products_id 4 const 2 Using where 1 SIMPLE p2c index NULL ptc_catidx 4 NULL 36675 Using index Thank you David Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.