Home
/
Database Support
/
How to reduce the database size of a Magento application?

How to reduce the database size of a Magento application?

It is common for a Magento database to grow large in size and slow down the website. In this article, we cover the most common problems and their possible solutions.

Create a backup

In order to create a backup go to Site Tools > Security > Backups. Choose any name for the backup under the Create New Backup > Backup Name and press the Create Backup button.

In case your hosting plan does not include the feature to create on-demand backups, you can navigate to Site Tools > Site > MySQL > PHPMyAdmin > Access. A new browser window will open with the PHPMyAdmin tool.

From there, select your database from the menu on the left and click Export > Go. The database backup will be downloaded to your computer and can be restored if this is needed.

This step is important because in case anything goes wrong with the database optimization that you will be performing, you can restore the database from the available backup.

If you haven’t done any changes to the website in the past 24 hours and there are no new registrations and orders on it, you can skip that step and just restore the automatic backup that we have created for you. We keep up to 30 daily backups for Shared hosting accounts and up to 7 daily backups for Cloud servers.

Optimize your database tables

It is possible that the Optimize database query can reduce the size of your website’s database tables.

To optimize the website’s tables, go to Site Tools > Site > MySQL > PHPMyAdmin > Access, select your database from the menu on the left, select all the tables with the Check All button on the bottom of the page. Then, select the Optimize option from the With selected: dropdown menu.

Do not worry about the warning that InnoDB does not support optimization – InnoDB does not support the optimization in the same way that it is performed compared to the MyISAM engine. Instead, MySQL will recreate the table and that action will have the same effect as the optimization of a MyISAM database table.

Remove logs from the database

There is a known Magento problem where the application stores a large number of data logs. The database can grow in size with time due to the logs. The logs may be useful but in most cases, they are not needed. It is recommended to regularly check these database tables.

Magento has built-in functionality that can remove logs older than a certain number of days, based on your preference. The feature can be accessed from Magento’s Backend > System > Configuration > Advanced > Log Cleaner.

Ensure that the Enable Log Cleaner option is set to Yes. Save Log, Days is the setting that determines how many days the logs will be kept.

Magento Logs are kept in the following database tables:

log_customer
log_visitor
log_visitor_info
log_url
log_url_info
log_quote
report_viewed_product_index
report_compared_product_index
report_event
catalog_compare_item

In case any of these tables are large in size, it is recommended to perform maintenance by scheduling the built-in Log Cleaner.

Check the core_url_rewrite database table

This specific table can grow big because of a well-known problem with the code in some Magento versions. This table stores the information about the rewrite rules (used for custom URL addresses for your products and pages). Because of this, the database table can grow big in size.

Consider upgrading your Magento to the latest stable version of the branch to resolve this. As an alternative, you can consult with your developer.

Protect your forms with Captcha

It is a common case where malicious bots submit spam comments and fake user registrations. This information is stored within your website’s database and can result in an increase in its size.

Such information should be deleted and all forms should be protected with Captcha. The Captcha would prevent the bots from submitting comments and register to your website.

Other possible situations

It is natural for a database to grow bigger with time and it, of course, depends on the number of products, registered users, comments, and orders. The more information your Magento store has, the larger the database would be.

In some cases, the big database is just a result of the information that you are storing and there isn’t much that can be done to reduce it, besides removing content from your website. As an alternative, upgrading to a plan that suits your website’s needs is also a solution.

In case the suggestions above do not apply to your website, you should consult with an experienced Magento developer.

What should I do in case there are problems with my website after a database optimization?

Always check your website for potential problems after applying changes to its database or after installing/removing various plugins or modules. If you notice anything not working properly, you could restore the website to the state prior the performed actions from the backup that you already have created.

Share This Article