Cleaning WordPress Database after WPML Removal with WP-CLI

by

After you have deleted and uninstalled the WPML plugins and its accompaying add-ons you will want to clean the database too. This particular installation I cleaned did not have WooCommerce or the WPML WooCommerce add-on so any cleanup specific to that is unfortunately not in this guide. However, if you are transitioning out of WPML for performance reasons and would like help cleaning up your database please do feel free to reach out on here. You can use phpMyAdmin or Adminer to clean up WPML as well if you prefer.

Back up your database first and test thoroughly on a staging site before you continue!

Remove WPML Database Tables and Options

In these sections we will remove the WPML relevant rows from the wp_options table and then the WPML dedicated database tables themselves.

If you want you can gather the autoloaded data count using the wp doctor command (see tutorial) so we can see how much the autoloaded data reduces in wp_options after cleaning out the WPML plugins.

wp doctor check autoload-options-size --allow-root --skip-plugins --skip-themes

Output

+-----------------------+---------+------------------------------------------------------------+
| name                  | status  | message                                                    |
+-----------------------+---------+------------------------------------------------------------+
| autoload-options-size | warning | Autoloaded options size (1.05mb) exceeds threshold (900kb) |
+-----------------------+---------+------------------------------------------------------------+

We are already over the wp_options autoloaded data recommended threshold of 900 KB (see here on how to clean up autoloaded data).

Cleaning WPML wp_options

WPML uses both the icl and wpml text strings to store its settings in the wp_options table.

We will start by removing the rows containing icl.

wp db query "SELECT COUNT(*) FROM $(wp db prefix)options WHERE option_name LIKE '%icl%'"

10 total rows containing icl can now removed now that WPML is uninstalled from your site.

+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+

Delete them with this command

wp db query "DELETE FROM $(wp db prefix)options WHERE option_name LIKE '%icl%'"

Now let’s remove the rows containing wpml.

Again let’s count them

wp db query "SELECT COUNT(*) FROM $(wp db prefix)options WHERE option_name LIKE '%wpml%'"

10 total rows containing wpml can now removed now that WPML is uninstalled from your site.

+----------+
| COUNT(*) |
+----------+
|       40 |
+----------+

Delete them with this command.

wp db query "DELETE FROM $(wp db prefix)options WHERE option_name LIKE '%wpml%'"

Now we are done with cleaning the options table of the leftover WPML database rows.

Let’s check the autoloaded data count again.

wp doctor check autoload-options-size --allow-root --skip-plugins --skip-themes

Output

+-----------------------+---------+--------------------------------------------------------------------+
| name                  | status  | message                                                            |
+-----------------------+---------+--------------------------------------------------------------------+
| autoload-options-size | success | Autoloaded options size (785.78kb) is less than threshold (900kb). |
+-----------------------+---------+--------------------------------------------------------------------+

That is over 100 KB saved in autoloaded data after cleaning up the database of WPML data :).

Removing WPML Database Tables

Let’s get a list of the WPML database tables which all contain the string icl.

wp db tables --all-tables --allow-root | grep icl

This particular site has a custom non-default WordPress database prefix of infom

infom_icl_content_status
infom_icl_core_status
infom_icl_flags
infom_icl_languages
infom_icl_languages_translations
infom_icl_locale_map
infom_icl_message_status
infom_icl_mo_files_domains
infom_icl_node
infom_icl_reminders
infom_icl_string_packages
infom_icl_string_pages
infom_icl_string_positions
infom_icl_string_status
infom_icl_string_translations
infom_icl_string_urls
infom_icl_strings
infom_icl_translate
infom_icl_translate_job
infom_icl_translation_batches
infom_icl_translation_status
infom_icl_translations

Script using WP-CLI to remove the WPML dedicated database tables.

WPMLTABLES=($(wp db tables --all-tables --allow-root | grep icl))

# loop through the options and create the query equivalent
for WPMLTABLE in ${WPMLTABLES[@]}
do
    echo "Dropping ${WPMLTABLE}"
    wp db query "DROP TABLE ${WPMLTABLE}" --allow-root
done

Output

Dropping infom_icl_content_status
Dropping infom_icl_core_status
Dropping infom_icl_flags
Dropping infom_icl_languages
Dropping infom_icl_languages_translations
Dropping infom_icl_locale_map
Dropping infom_icl_message_status
Dropping infom_icl_mo_files_domains
Dropping infom_icl_node
Dropping infom_icl_reminders
Dropping infom_icl_string_packages
Dropping infom_icl_string_pages
Dropping infom_icl_string_positions
Dropping infom_icl_string_status
Dropping infom_icl_string_translations
Dropping infom_icl_string_urls
Dropping infom_icl_strings
Dropping infom_icl_translate
Dropping infom_icl_translate_job
Dropping infom_icl_translation_batches
Dropping infom_icl_translation_status
Dropping infom_icl_translations

Done!

Enjoy your cleaner, lighter and faster database 🙂

Sources

WPML Database Tables Documentation