Deleting Unused WPML Translation Strings with(out) WP-CLI

by

WPML is crazy popular and by far the preferred and dominant translation engine for WordPress. It is quite common for clients to complain that WPML is slow for one reason or another. One of the primary causes of a slow WPML site is the translation string tracking. By default, WPML wants to make you happy so it automatically tracks strings for you to translate. This also leads to a lot of rows in the database. Since cleaning unused translation strings is one of the best ways to speed up WPML, we are going to do that today with or without WP-CLI.

Delete Unused WPML Translation Strings

Before you do anything we need to take a backup of the database

wp db export before-translation-clean.sql --all-tablespaces --allow-root

We now need to make some modifications to the wp-config.php file before we can remove the untranslated WPML strings in whatever languages you have enabled.

WPML wp-config.php Modifications

In wp-config.php we need to add the following constants which all relate to the status of the translation strings which have been registered and tracked by WPML.

define( 'ICL_STRING_TRANSLATION_PARTIAL', 2 );
define( 'ICL_STRING_TRANSLATION_COMPLETE', 10 );
define( 'ICL_STRING_TRANSLATION_NEEDS_UPDATE', 3 );
define( 'ICL_STRING_TRANSLATION_NOT_TRANSLATED', 0 );
define( 'ICL_STRING_TRANSLATION_WAITING_FOR_TRANSLATOR', 1 );

We can actually set these with WP-CLI using the wp config set command.

wp config set ICL_STRING_TRANSLATION_PARTIAL 2 --allow-root

You will see output like this saying the constant has been added to wp-config.php.

Success: Added the constant 'ICL_STRING_TRANSLATION_PARTIAL' to the 'wp-config.php' file with the value '2'.

Now we can do the rest to be fully prepared for the WPML untranslated strings cleanup

wp config set ICL_STRING_TRANSLATION_COMPLETE 10 --allow-root
wp config set ICL_STRING_TRANSLATION_NEEDS_UPDATE 3 --allow-root
wp config set ICL_STRING_TRANSLATION_NOT_TRANSLATED 0 --allow-root
wp config set ICL_STRING_TRANSLATION_WAITING_FOR_TRANSLATOR 1 --allow-root

Now we can move on to deleting the translation strings

Deleting Untranslated Strings from WPML Database

We are going to need to run a few MySQL queries to delete the untranslated WPML strings.

Let’s count the number of untranslated strings

SELECT COUNT(*) FROM wp_icl_strings WHERE status IN (3, 1, 0);

Here is the WP-CLI version

wp db query "SELECT COUNT(*) FROM $(wp db prefix --allow-root)icl_strings WHERE status IN (3, 1, 0);" --allow-root

This query removes the strings which do not meet the criteria of ‘translated’ according to the constants we defined in wp-config.php earlier.

Make sure to adjust your database prefix in these queries if it is not the default value of wp_
DELETE FROM wp_icl_strings WHERE status IN (3, 1, 0);

Here is the WP-CLI version

wp db query "DELETE FROM $(wp db prefix --allow-root)icl_strings WHERE status IN (3, 1, 0);" --allow-root

Here is the next count query

SELECT COUNT(*) FROM wp_icl_string_translations WHERE string_id NOT IN (SELECT id from wp_icl_strings);

Here is the WP-CLI version

wp db query "SELECT COUNT(*) FROM $(wp db query --allow-root)icl_string_translations WHERE string_id NOT IN (SELECT id from $(wp db prefix --allow-root)icl_strings);" --allow-root

Now we can delete these too.

DELETE FROM wp_icl_string_translations WHERE string_id NOT IN (SELECT id from wp_icl_strings);

Here is the WP-CLI version

wp db query "DELETE FROM $(wp db prefix --allow-root)icl_string_translations WHERE string_id NOT IN (SELECT id from $(wp db prefix --allow-root)icl_strings);" --allow-root

Now we just need to truncate (meaning empty) these tables.

TRUNCATE wp_icl_string_packages;
TRUNCATE wp_icl_string_positions;

Here is the WP-CLI version

wp db query "TRUNCATE $(wp db prefix --allow-root)icl_string_packages;" --allow-root
wp db query "TRUNCATE $(wp db prefix --allow-root)icl_string_positions;" --allow-root

Now let’s just clean up the wp-config.php.

Finishing up the WPML Untranslated Strings Cleanup

You can now remove the constants we added in wp-config.php to keep it clean.

wp config delete ICL_STRING_TRANSLATION_PARTIAL 2 --allow-root
wp config delete ICL_STRING_TRANSLATION_COMPLETE --allow-root
wp config delete ICL_STRING_TRANSLATION_NEEDS_UPDATE --allow-root
wp config delete ICL_STRING_TRANSLATION_NOT_TRANSLATED --allow-root
wp config delete ICL_STRING_TRANSLATION_WAITING_FOR_TRANSLATOR --allow-root

We need to disable the automatic registration of strings and prevent the database tables from filling up again.

In the admin sidebar navigate to the WPML > String Translation > Auto register strings for translation and click Edit

Uncheck the top empty boxe to uncheck all of the boxes underneath and click Apply so that automatic tracking is disabled.

The Auto register strings for translation box should now show Strings from all text domains are excluded.

The last step is to recreate the tables we truncated.

To recreate these tables go to WPML > Support and find this text ‘For advanced access or to completely uninstall WPML and remove all language information, use the troubleshooting page’ and click the troubleshooting hyperlink.

Once on the troubleshooting page find the Recreate ST DB cache tables button and click it.

That’s it! WPML should no longer track the automatic registration of strings to translate and all of the untranslated strings are no longer taking up space in the database 🙂

Sources

WPML Support Deleting Untranslated Strings
WP-CLI wp config Documentation