2

I'm working with magento 1.9.2 website for a manufacture company which has over 1M product (each product have 5+ customer grouping price and trier pricing) and 5k+ customer group.

I did all the basic performance measures.

Buyed a dedicated server, now website performs good except the indexes. while reindexing 'Product Prices' takes one or more days (some times it never stopped) and pricing is not being updated properly in frontend (note: prices updated regularly through api).

So i planned to store the pricing data's to my custom table and i reflected this to frontend. Even all the products stored all the prices (0.00) in related tables (Magento tables).

This also takes too much time to re-indexing all.

What I tried Previously?

Truncated the price related tables.

TRUNCATE TABLE `catalog_product_index_price`;
TRUNCATE TABLE `catalog_product_index_price_bundle_idx`;
TRUNCATE TABLE `catalog_product_index_price_bundle_opt_idx`;
TRUNCATE TABLE `catalog_product_index_price_bundle_opt_tmp`;
TRUNCATE TABLE `catalog_product_index_price_bundle_sel_idx`;
TRUNCATE TABLE `catalog_product_index_price_bundle_sel_tmp`;
TRUNCATE TABLE `catalog_product_index_price_bundle_tmp`;
TRUNCATE TABLE `catalog_product_index_price_cfg_opt_agr_idx`;
TRUNCATE TABLE `catalog_product_index_price_cfg_opt_agr_tmp`;
TRUNCATE TABLE `catalog_product_index_price_cfg_opt_idx`;
TRUNCATE TABLE `catalog_product_index_price_cfg_opt_tmp`;
TRUNCATE TABLE `catalog_product_index_price_downlod_idx`;
TRUNCATE TABLE `catalog_product_index_price_downlod_tmp`;
TRUNCATE TABLE `catalog_product_index_price_final_idx`;
TRUNCATE TABLE `catalog_product_index_price_final_tmp`;
TRUNCATE TABLE `catalog_product_index_price_idx`;
TRUNCATE TABLE `catalog_product_index_price_opt_agr_idx`;
TRUNCATE TABLE `catalog_product_index_price_opt_agr_tmp`;
TRUNCATE TABLE `catalog_product_index_price_opt_idx`;
TRUNCATE TABLE `catalog_product_index_price_opt_tmp`;
TRUNCATE TABLE `catalog_product_index_price_tmp`;
TRUNCATE TABLE `catalog_product_index_tier_price`;

if i truncated the above table, then the product collection not showing in frontend.

Now what i want to know?

  1. when truncated the above tables why the products not showing?
  2. how to avoid this issues?
  3. where i go to remove the join query to remove the pricing related collections?

I am looking for permananet solutions for avoiding to store prices, customer prices and trier prices in magento db.

Thanks

Improve this question

2 Answers 2

Reset to default
2

One thing we typically do is have 2 servers, 1 for website/files and 1 for the database itself.

It would be worth doing a repair and optimize on the tables within the database with something like

mysqlcheck -u -p --auto-repair

Also i'd recommend giving mysql tuner a try, it will check over your database setup and then give you recommendations on things to improve for performance and stability: https://github.com/major/MySQLTuner-perl

Improve this answer
1

Please review the refman for TRUNCATE purpose. You will find you REMOVED all rows from each table listed. Probably a pretty bad consequence resulted. Whoever advised you to do this should be avoided in the future ( they will only get you into more TROUBLE).

For tuning assistance, please review my profile, Network profile for contact info and get in touch with me by Skype.

Improve this answer
1
  • @sathish When you are ready to improve performance of your MySQL instance, let me know.
    – Wilson Hauck
    Commented Nov 13, 2018 at 11:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.