Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

What are these two SQL queries for?


rs2k

Recommended Posts

select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price, pd.products_name from products p left join specials s on p.products_id = s.products_id left join products_description pd on p.products_id = pd.products_id, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '507' and p.products_status = '1' and pd.language_id = '1' order by p.products_date_added desc limit 9

 

 

select distinct p.products_id, pd.products_name from products p, products_description pd, products_to_categories p2c, categories c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '507' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit 10

 

 

 

The first one takes a little over a second to run and the second one takes about half a second to run.

Link to comment
Share on other sites

There getting product info from database, do you have a lot of producrts?

Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Link to comment
Share on other sites

There getting product info from database, do you have a lot of producrts?

 

I have about 47,000 products.

 

With caching enabled my store uses between 33 - 37 queries per page and it can take anywhere from 0.8 seconds to 3 seconds to execute those queries depending on how long those two product queries take. Is there anyway to cache those two queries? Caching the categories has sped up the store quite a bit. When you come across a category that hasn't been cached yet it takes between 2 and 5 seconds to execute about 1200 queries.

 

Without those two queries the store would only take 0.3 seconds to execute the rest of the queries.

Link to comment
Share on other sites

Without those two queries the store would only take 0.3 seconds to execute the rest of the queries.

Did you add the indexes on the tables as mentioned in upgrade.html in the osC 2.2-rc2a download? The query that has the specials in them is infamous for making sites slow without those indexes.

Link to comment
Share on other sites

Did you add the indexes on the tables as mentioned in upgrade.html in the osC 2.2-rc2a download? The query that has the specials in them is infamous for making sites slow without those indexes.

 

Yes, I did that before I started this thread. It did make a difference. I deleted 5 fields in the product table that I added that didn't really need to be there. That also sped the process up a little bit. I'm going to keep looking for ways to speed thing up.

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...