How to reduce the database size of a custom application?
Table of Contents
In this article, we will share some tips on how to reduce the size of any application other than WordPress. We have covered the WordPress database optimization in our articles: ‘How to reduce the database size of a WordPress application?‘.
Create a backup
Go to Site Tools > Security > Backups. Choose any name for your 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. From the tool, 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 your website, 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.
Check your website’s database tables
Navigate to Site Tools > Site > MySQL > PHPMyAdmin > Access. Select your database from the menu on the left and sort the tables by size by clicking on the Size column. The largest tables will appear at the top and this information will be useful for the investigation.
Check the names of the largest tables. Usually, the database tables names can bring an insight into what type of information they store.
For example, it often happens that logs are being stored in the database and the database table contains the log keyword. Make sure that no logs are being stored in the database because it can grow very large with time.
Check your website’s settings for an option to clear the logs and store them in a file. It is possible that there isn’t such an option and you should contact the website’s developer for further information.
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.
Remove data that is no longer needed
You could also review your website’s content and remove any data that is not needed. Usually, most of the information is stored in the database, and clearing it will reduce its size.
Other possible problems
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.
In some cases, the large 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 needs is also a solution. In case the suggestions above do not apply to your website, you can consult with an experienced developer.
What should I do if 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 to the performed actions from the backup that you already have created.