Deleting Drupal fields with large MySQL tables

I just came across the case that I need to remove several fields from a content type in a Drupal 9.

Those are simple to do, aren't they?

On your site use the Drupal user interface, delete the fields and export the configuration. Then on the production site import the configuration and it will automatically delete the fields, their tables and their mysql data. Isn't it that simple?

Well no, it is not that simple when you have thousands of nodes and the tables of those fields weigh several GB in the database.

What problems have I encountered?

Basically Drupal first tries to truncate each of the tables (and its corresponding revision table).

This is a problem, because a truncate of a very large table takes a long time. So Drupal will take a very long time to try to remove those fields.

It takes so long that the "drush cim" command gives an error and leaves the database locked.

How did I solve it?

Truncating large tables, with millions of rows, in InnoDB can be very slow. Fortunately, there is a little trick to speed this up (thanks Google).

The trick is to first create a table that has the same structure as the table you are truncating:

CREATE TABLE `new_table` LIKE `bigtable`;

Then you can change the table to be truncated to the empty table:

RENAME TABLE `bigtable` TO `old_table`, `new_table` TO `bigtable`;

After this, your system will use the new (and empty) table, and you can quickly delete the old large table:

DROP TABLE `old_table`;

There, you have successfully truncated your huge table.

So, in Drupal I have to do those querys to empty the tables of the fields I want to delete, then I can do the "drush cim" to import the drupal configuration, which will delete the tables.

Why not make a Drop?

If you drop directly from the tables, then Drupal may give you an error when importing the configuration, because it does not find the tables.

Apart from the fact that if it is the production website, then users who are trying to view the pages of those nodes will get errors. You have deleted tables before importing the Drupal configuration, therefore, Drupal will try to get that data from the fields that no longer exist in the database. This leads to web users seeing a lot of errors or the web not loading at all.