Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Help finding Query


DAVID3733

Recommended Posts

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

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...