Cleaning Up Database to Speed Up Development Cycles

If you are developing websites, you would like to make this process as pleasant as possible. You probably agree that the time spent on the work you do is also an important factor. Therefore, all of us developers want to develop websites as quickly as possible while spending as little effort and energy as possible.
In this article, we will look at Drupal performance optimization and how you can clear the database tables of Drupal modules and MySQL tables. The following information will help you speed up development cycles. And also, among other things, it will help you feel a little happier after a hard day's work! But first of all, you need to understand what a database table is.

Lean Drupal database

You may have already noticed that the site has a table with a database. It is called vidhist. Have you ever wondered what this table was created for? Every time you perform some action on the site, more and more data is entered into the database. Let's talk about what this data is used for.
For example, you watched a video. All your actions related to the video are recorded as data in a table. The saved interaction data show the users which videos we have already watched. This helps save time as we don't have to watch every video to find out which ones we've already watched and which we still need to. And it's a cool feature!
Also, in the vidhist table, you can see the usage history for a certain period.
What if, for some reason, you didn't watch the video? In that case, don't worry! The table makes it possible to start watching the video from where the user left off last time.
All of these features are great and make life much easier. On the other hand, there is one problem that eats up all of our precious time. What is it?

Overflowing database tables waste time

The vidhist table gradually grows and grows over time. More and more data is stored in the database. And this subsequently leads to the fact that the site's development takes a lot of time.
However, to our great delight, entering data into this table is not only possible. You can remove everything unnecessary or clean up the table. Isn't that great news? A small problem arises when synchronizing the database with the local host for development. Creating a backup can take a considerable amount of time, sometimes even a long time.  
Drupal includes caching modules such as Internal Page Cache and Dynamic Page Cache. They work reliably and often speed up work. However, in some cases, the cache in the database can cause some problems. For example:

• Incorrect page output. This happens if the cache is corrupted;
• Deprecated page output. This problem occurs when the stale cache is kept for too long;
• Long page opening;
• 30 minutes is the time it will take you to sync in the case of a local environment;
• Cloning a database from one environment to another is very slow;

You may ask: "What if these database-clean Drupal modules are not included on your site?". Well, that will keep your database problems. It should be understood that Drupal will somehow use the cache for various calculations.

How can this situation be resolved?

Let's consider the next disadvantage in more detail. Tugboat.qa is used for the pull request for the automatic preview environment. Each preview item uses approximately 8 GB of space. Because of this, the limit of the allowed limit is reached. And this is even though it takes less time to create a new preview, although the database size at the same time is quite large. Updating the base preview image takes eighteen to twenty minutes each night.

• We occasionally use the multidev feature in Pantheon. These environments, like the ddev pull command, take a long time to create. And partly because of the large size of the table.
• The integration test suite takes eleven minutes to complete. Plus, there is a need to create a testing environment. This results in tests taking at least as much as 40 minutes to run!

 Of course, all this is partly just the work of robots that can work in the background. On the one hand, this is a plus, as it makes it possible to do other work. But still, there are some areas where the database really affects performance a lot. That is why some changes have been made to the program that will help all its users in the future.

Clean up the database in Drupal

So, there is a certain algorithm of actions to clear the data table.
Every time a database is cloned to another environment, the following steps must be followed:

• Check if this is definitely not a working environment
• To query the database, the Bootstrap Drupal command is enough

Next, you must delete all data from the vidhist table except those saved within the last month. Note that we are making multiple queries instead of a single DELETE WHERE. You need to do this because TRUNCATE is several times faster.
And what do we get as a result? As a result, the vidhist table, being in any of the non-production environments, becomes several times smaller than it was. Yes, this is a small change. But believe me, it matters a lot!
The above operations also help to reduce disk usage on Tugboat.qa. If you remember, Tugboat wants the database from the Pantheon test environment. So, after the work on the Drupal database cleanup, Tugboat gets a smaller table. So build times for previews are 1 minute faster! On average, views are now generated in one minute and eleven seconds!

Mysql cleans up the database

What are the best ways to clear the table in MySQL? There are only two of these methods.
You can clear the table by entering one of the commands: TRUNCATE or DELETE.
Note that these commands are not the same. Each performs a different function and is very different from one another.

So what is their difference?

• The TRUNCATE command is a Data Definition Language statement, that is, a data definition language. The operators of this language have the ability to manipulate database objects. Namely: delete, create or rename them.
• The DELETE command is a Data Manipulation Language statement, data manipulation language. The operators in this language allow you to insert, delete, modify, retrieve, or update data in a database.

Here is a more in-depth explanation of what each command does.

TRUNCATE command

The TRUNCATE command allows you to remove all data from a table and all rows. This command also makes entries in the transaction log for each deleted page. The significant advantage of the command is that it is faster than DELETE. Also, TRUNCATE resets identifiers. Finally, this command locks the table and page before deleting them.

DELETE command

What about the DELETE command? Yes, it is inferior to TRUNCATE in many ways. What exactly? DELETE deletes only a certain part of the data, deletes only one row at a time, and does not perform the function of resetting indicators. And the saddest thing is that it works slower.
There is no doubt that each specific case requires a unique solution. We cannot say only to use DELETE or only to use TRUNCATE. In their own way, both teams are good. Therefore, you need to look at the situation.

Conclusion

Sometimes we think, “This is just a few minutes. It's easy for me to wait." But remember that a few minutes can turn into hours or days. So, what can we say about cleaning up the Drupal database, given everything we've discussed in this article?
Look at your website development activities. Is it possible to improve something in your actions? For example, take care of cleaning up the database! This will double your productivity! Spend your time wisely by getting the most out of every website you develop with Drupal! Need help? Contact our Drupal site audit team to find an answer.