Optimize Magento Database

Got a shock when you saw an old Magento Database with only hundred  products but 1 GB size?? Stop worrying. The client has forgot to set up the log clean crons or used so many cache extension that log clean up has stopped working. You need to optimize magento database. In order to clean up the waste and see the difference you can follow these small steps.

1) Sum Up What is the Size of the Database

For this you need to run a simple SQL Query:

SELECT table_schema "Database Name", SUM( data_length + index_length) / 1024 / 1024 "Database Size in MB" FROM information_schema.TABLES WHERE table_schema = 'your_database_name' ;

You will get the size of database size in MB. Copy and keep this in notepad, This will help you analyse the difference of size after you have optimized.

2) Now you Need to Truncate These Tables in Your Magento Database

dataflow_batch_export
dataflow_batch_import
log_customer
log_quote
log_summary
log_summary_type
log_url
log_url_info
log_visitor
log_visitor_info
log_visitor_online
report_viewed_product_index
report_compared_product_index
report_event

SQL Query:

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;

3) Repeat Step 1

You need to calculate the difference in database size to be sure.

Aha! Noted the difference? Clean up and speed up database and make your boss happy. So enjoy!! 🙂