Magento – How to optimize Magento database

databasemagento-1.9

I have magento version 1.9.2.1, 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.?

Best Answer

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 :)

Related Topic