How to optimize Magento database

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

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:

adminnotification_inbox,
aw_core_logger,
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,
index_event,
report_event,
report_viewed_product_index,
report_compared_product_index,
catalog_compare_item,
catalogindex_aggregation,
catalogindex_aggregation_tag,
catalogindex_aggregation_to_tag

2. 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.