Note:
Don’t forget to back up your database before launching any kind of optimisation process.
We will discuss the three ways tp optimize your Magento Database
A.About Magento logging
By cleaning the database log tables and tweaking Magento’s log settings. You use Magento’s administration interface to optimize log settings.
Magento comes with a PHP script that can be used from the command line to clean database log tables and determine their size.
B.Magento Log Types
There are two log types in Magento. The first one is System and Exception log files. They’re disabled by default. So, if you want to have a detailed report on any system events, you’ll need to enable them.
Go to System → Configuration → Advanced → Developer → Log Settings and choose Yes under the Enabled drop-down setting, and click on Save Config:
The logs will be saved in the ‘var/log’ directory, don’t forget to check and clean them periodically, as well as logs from ‘var/report’.
The second type is what we talk about today saves logs on any events related to customers’ activities. The data is saved in Magento Community Edition (CE) database tables listed below:
log_customer log_visitor log_visitor_info log_visitor_online log_summary log_summary_type log_url log_url_info log_quote Index_event report_event report_viewed_product_index The tables contain lots of useful info like a customer’s login/out date and time, the URLs they visited as a part of a session, the products they compared, the quotes they made, their actions in-store, in general, etc. Magento Enterprise Edition (EE) has two more tables, namely enterprise_logging_event and enterprise_logging_event_changes. All this data accumulates and hampers your Magento if not cleaned timely. Thus, the bigger the store, the more unnecessary info overloads your website. So, here are three ways to solve this. Clean Magento Database Logs via the Admin Panel The steps you need to take here are: 1. Log in to your Admin panel. 2. Go to System → Configuration. 3. Go to the left sidebar, find the Advanced menu and click on System. 4.Choose the Log tab from the menu and set Enable Log to Yes, and set up a desired automatic log cleaning frequency. 5.Click on Save Config. Clean Magento Database Logs via MySQL Client 1.Enter the phpMyAdmin panel from your hosting control panel. 2.Enable the checkboxes for the next tables with logs: log_customer log_visitor log_visitor_info log_visitor_online log_summary log_summary_type log_url log_url_info log_quote Index_event report_event report_viewed_product_index report_compared_product_index catalog_compare_item dataflow_batch_export dataflow_batch_import Add enterprise_logging_event and enterprise_logging_event_changes if you use EE. Go to the action drop-down With the Selected menu at the bottom of the page and choose Empty. Then click Yes on the appeared confirmation page. You now truncated the selected tables. Now go to the top of the page and click on the Structure tab. Tick the same tables you’ve just truncated, and under the With Selected list, click on Optimize. Clean Magento Database Log Tables Using the Command Line You can make the Magento database cleanup through the log.php file in Magento /shell. It can be run both manually and by using a cron job. When you run the cleaning manually, do the following: 1.Go to the root directory (terminal) and use the command: php -f shell/log.php clean 2.See the results: php -f shell/log.php status Disable Magento Logging 1.Open the app/etc/local.xml file. 2.Paste the next patch before the </config> tag, and then save the local.xml file. 3.Navigate to System → Configuration → Advanced → Disable Modules Output and choose Disable for Mage_Log. 4.Flush your Magento cache.