dianys Posted June 30, 2010 Posted June 30, 2010 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!
Guest Posted June 30, 2010 Posted June 30, 2010 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
chadcloman Posted June 30, 2010 Posted June 30, 2010 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.
chadcloman Posted June 30, 2010 Posted June 30, 2010 1- install this contribution http://www.oscommerce.com/community/contributions,2561 The forum software doesn't like that link, here's a clickable version: Page Cache v1.0 - MS2 Check out Chad's News.
dianys Posted June 30, 2010 Author Posted June 30, 2010 i have a dedicated mysql server :( that problem persit only loading product_listing.php
Jan Zonjee Posted June 30, 2010 Posted June 30, 2010 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.
RMD27 Posted May 19, 2012 Posted May 19, 2012 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!
Jack_mcs Posted May 19, 2012 Posted May 19, 2012 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
Recommended Posts
Archived
This topic is now archived and is closed to further replies.