I have magento version, and I have greater than 30000 products in my store. My magento databse size raise to 3GB approximate.

For database optimization What I already did :

1) clean log tables

2) follow https://magento.stackexchange.com/a/18690/14039

But still database size is 2.7 GB.

When I checked products tables, these have multiple entry for one product corresponding to store and customer group. For Example : I have 4 store and 6 customer groups then there are total 24 rows for one product in table catalog_product_index_price.

Is there any way to set global scope for all customer groups so that there will be only 4 rows in catalog_product_index_price tables?

Or any other way to optimize products tables like catalog_product_index_price, catalog_product_entity_varchar, catalog_category_product_index, catalog_product_index_eav_idx, catalog_product_index_eav, catalog_product_index_price_idx, catalog_product_entity_text etc.?

  • Try to use use Fabrizio Branca's AOE Quote Cleaner github.com/fbrnc/Aoe_QuoteCleaner Allows you to clean up the quote tables in a scheduler task. Check the system configuration for the settings on how old quotes (from anonymous and from logged in users) can be before they will be deleted. Apr 28, 2017 at 15:12

4 Answers 4


Run below query into your database

SET foreign_key_checks = 0;
TRUNCATE dataflow_batch_export;
TRUNCATE dataflow_batch_import;
TRUNCATE log_customer;
TRUNCATE log_quote;
TRUNCATE log_summary;
TRUNCATE log_summary_type;
TRUNCATE log_url;
TRUNCATE log_url_info;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;
TRUNCATE log_visitor_online;
TRUNCATE report_viewed_product_index;
TRUNCATE report_compared_product_index;
TRUNCATE report_event;
TRUNCATE index_event;
TRUNCATE catalog_compare_item;
SET foreign_key_checks = 1;

Hope it helps :)

  • He already mentioned that He has clear the log tables , So Your answer has no meaneing at here. May 10, 2017 at 13:23

Well it really depends on what do you mean by optimising database. If you are refering to improving speed of magento application then deleting index table will degrade magento performance until magento regenerates them. If you want better application performance then enable flat table and flat category structure from magento backend. This setting can be found under System->Configuration->Catalog Now enabling this does increase your database size but it improves the performance of the application. To further improve application performance remove any attribute sets or attributes not being used. If you want to reduce Database size consider following

  • deleting old orders and quotes (after having a backup)
  • deleting old redirects that are not being used from url rewrite
  • make sure that you are not using database for session storage.(Redis)
  • Use something else for catalog search like elastic search or solaris

Sometimes it happens so that the database really becomes trouble for Magento developers. Removing useless entries from Magento database which is mentioned above is a fantestic idea. Moreover, there is a guide on Magento database improvement. In this guide, the author compares MySQL, MariaDB and Percona and provides tips on how to tune your database. Find it here.


Some of the steps I did when optimizing the Magento 1 database

  1. Truncate these tables:
  1. Run the MySQL Optimize command/statement through PHPMyAdmin or through Terminal on some of the tables that you have deleted a large part of the table. For example, OPTIMIZE my_table. Using this, you can reclaim unused space and defragment the data file for optimal performance.

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.