Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

A big problem. slow (20-30 sec)


dianys

Recommended Posts

Posted

Hello,

 

I`m Dianys and i have a big problem with oscommerce. I have 1.000.000 products in db and 460.000 categories. I have a car tools auto. Ex. Audi -> A4 -> 1.6 -> Diesel -> 2000 - Today HERE IS THE PROBLEM-> product listings.

I waiting 20-30 seconds here.. please help me.

In slow query log i see:

select p.products_image, pd.products_name,  p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join specials s on p.products_id = s.products_id, products_to_categories p2c where  p.products_status = \'1\' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = \'1\' and p2c.categories_id = \'28645\' order by pd.products_name limit 0, 20		/home/automoto/public_html/index.php		17.5585799217		June 30, 2010, 8:08 pm
select p.products_image, pd.products_name,  p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join specials s on p.products_id = s.products_id, products_to_categories p2c where  p.products_status = \'1\' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = \'1\' and p2c.categories_id = \'46606\' order by pd.products_name limit 0, 20		/home/automoto/public_html/index.php		18.2547380924		June 30, 2010, 8:08 pm

 

Thanks to all!

Posted

We recommend to doing this:

 

1- install this contribution http://www.oscommerce.com/community/contributions,2561

2- Run this query in your db

 

ALTER TABLE `products_to_categories` DROP PRIMARY KEY;

ALTER TABLE `products_to_categories` ADD INDEX `idx_p2c_categories_id` ( `categories_id` );

ALTER TABLE `products_to_categories` ADD INDEX `idx_p2c_products_id` ( `products_id` );

 

 

Thanks

Posted

I'm not a database optimization expert, but here are some things you may find helpful.

 

Consider performing an OPTIMIZE command on all the tables in your database. This is something that should typically be done on a monthly basis.

 

You may also want to look in to osCommerce's caching feature. That would reduce the load on your database server.

 

Other things would be to work on improving your MySQL server. Obviously you have a large database, but if you're sharing a database server with other users, that would exacerbate the situation. So you want a dedicated MySQL server with fast hardware and (even more importantly) fast hard drives.

Check out Chad's News.

Posted

i have a dedicated mysql server :( that problem persit only loading product_listing.php

Posted

In slow query log i see:

Did you also add this index on specials:

 

alter table specials add index idx_specials_products_id (products_id);

 

This has helped a lot of people in the past with slow queries like that.

  • 1 year later...
Posted

We recommend to doing this:

 

1- install this contribution http://www.oscommerce.com/community/contributions,2561

2- Run this query in your db

 

ALTER TABLE `products_to_categories` DROP PRIMARY KEY;

ALTER TABLE `products_to_categories` ADD INDEX `idx_p2c_categories_id` ( `categories_id` );

ALTER TABLE `products_to_categories` ADD INDEX `idx_p2c_products_id` ( `products_id` );

 

 

Thanks

 

thank you from me as well!

Posted

Before others rush to install the Page Cache contribution, I don't recommend doing that. It never did work properly and the original author gave up on trying to fix it. I've removed it from a number of shops where it was causing problems. The alter specials is a good change though if you have an old shop. There are additional changes like that in the RC2 upgrade file.

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

Archived

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

×
×
  • Create New...