Magento database maintenance

Tips and Guides/ 17th Oct 2016/Will Cook-Martin

Magento Guides

If you have a sluggish Magento store, generally the advice is to implement caching, minimise file sizes, concatenate CSS and Javascript, use a CDN, and optimise server settings. Whilst good advice, it misses a key part of the jigsaw when it comes to website speed – your database.

Magento, like most modern websites, stores its information in a database. PHP is used to extract it and turn it into HTML, which your web browser then renders.

The larger your database is, the harder your server has to work searching through more records to find the correct information it needs in order to display a page to your web site user

The guide below details how you can perform Magento database maintenance using Magento’s admin panel or phpMyadmin

As always, make sure you backup your database before making any changes.

Did you know…

At Clook, one of our specialisms is providing Magento optimised hosting, if you’d like to find out more, please get in touch with us.

Clean your Magento database using the Magento admin panel

1
Log into the Magento admin panel

Log into your admin panel by visiting the admin URL

This is by default www.yourdomain.com/index.php/admin unless you specified otherwise when installing Magento

magento_login

2

Navigate to System > Configuration

magento_system_configuration

3

On the left hand menu, navigate to Advanced > System

magento_advanced_system

4

Choose “Yes” in the “Enable Log Cleaning” dropdown box.

Complete the rest of the settings as you see fit. Here I’ve set log files to be deleted after 30 days and for the database clean to be run daily at 01:00

magento_system_log_settings

Clean your Magento database using cPanel and phpMyAdmin

1
Login to your cPanel account

Navigate to your login page for cPanel. This is normally your domain name or IP address followed by /cpanel

e.g. http://yourdomainname.com/cpanel

login_cpanel

2

Click the phpMyAdmin link in the database section

cpanel_phpmyadmin

3

In the top left area of the page, click on the relevant database

phpmyadmin_select_db

4

On the right hand window, search for the following and tick the box next to the table name:

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

magento_select_tables

5

Then scroll to the bottom of the page and you’ll see a dropdown box “With selected:”

Select “Empty” from the dropdown menu.

phpmyadmin_empty_table

6

On the next page you’ll see the following warning

Confirm that the table names are correct and then click the “Yes” button on the right hand side of the page

magento_empty_db_tables

Summary

It is not unusual to see large Magento databases decrease significantly in size (we’ve seen some decrease by over 70%!) after performing Magento database maintenance.

If you want your Magento store to keep running smoothly, you should perform a Magento database purge regularly. Consider creating a Cron job to do this for you.

Will's our technical sales guy from the hills of Rossendale. A follower of Stoke City, father to Eddie, husband to Catherine and a fan of gadgets - oh and he's recently mastered the art of really slow DIY.
We have a very strong team here at Clook and we build on everyone's strengths and weaknesses, you have to be able to make a good brew too!

Chris James

Customer Login

Forgot Password? Cancel