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.
