Software Engineer and Enterprise Architect
Optimize WordPress database with WPML in 7 steps
This article gives you a brief overview of important topics related on how to optimize WordPress database with WPML.
WordPress is a very popular content management system with a vast plugin ecosystem. When it comes to multi-language plugins leaders are TranslatePress, WPML, Polylang, MultilingualPress. Each of these solutions has pros and cons, but they all increase complexity in the WordPress database.
Table of Contents
- Why is it important to optimize the WordPress database with WPML?
- How to optimize the WordPress database with WPML?
- Always update to the latest version on WordPress core and WPML plugin
- Proper database monitoring, a.k.a. Table Level Monitoring
- Learn more about WPML tables and optimize WordPress database with WPML
- Setup proper staging and test everything
- Use the WPML mechanism for database optimization
- Do proper research for your specific issue
- Optimize MySQL Queries
- How to optimize WordPress database in general
Why is it important to optimize the WordPress database with WPML?
When we are considering e-commerce services, database speed is very important for user experience. Very quickly we end up having lots of products. Furthermore, lots of product revisions, multiple images, comments, etc.
These are only some of the elements that influence web site speed and database optimization. Introducing a multi-language mechanism as an additional feature to e-commerce service (or online shop if you prefer better) creates an additional layer of complexity to be handled.
This additional layer of complexity is the reason for web site slowdowns. This is why most website owners spend large amounts of time thinking about various ideas to cope with optimizations.
Let’s take, as an example, WordPress based website, with a WooCommerce shop, with 100+ products. This site has several payment gateways, and a multi-language mechanism implemented with one of the popular multi-language plugins, such as WPML.
WPML support forums are full of various questions on how to optimize WPML database size. It’s important to understand that WPML is a premium and reliable product, but IT IS an additional layer to an already existing website.
This means that the quality of the site alone will, of course, influence the behavior of this or any other plugin.
However, website owners and developers can optimize databases. Even more, they can make their web sites run faster with WordPress + WPML + (probably) WooCommerce + various additional plugins. In order to separate concerns better we can divide optimizations into two sections:
- Database optimizations related directly to WPML plugin
- General WordPress database optimizations (that can be applied even if WPML or WooCommerce is not present)
How to optimize the WordPress database with WPML?
We can describe the WordPress database optimization process in several steps.
- Update the WordPress core and WPML plugin
- Monitor database performance
- Know your WPML tables
- Stage and test everything
- Use WPML settings and features
- Discover specific (edge cases) issues
- Optimize MySQL queries
Additionally to these steps, you can always improve other general things. For example, you can set up proper JS and CSS minification to boost your website performance. If you take care that your website loads fast, this will be beneficial for the whole user experience, including the WPML part.
Always update to the latest version on WordPress core and WPML plugin
The database size issue is a common issue with large-scale WPML web sites. We can resolve this with the latest versions of the WPML plugin and sub-plugins.
Having that in mind, you should update all WPML related plugins to the latest versions and manage to maintain your WordPress website in the right way.
This is indeed common advice, but lots of website owners won’t update their sites, either because of being afraid of bugs or because they don’t have a license anymore.
Proper database monitoring, a.k.a. Table Level Monitoring
On support forums, most of the users state that their database size increased after installing and setting up WPML.
This information is not enough to properly optimize the WordPress database with WPML.
Firstly, the database is built with multiple tables. Furthermore, each of these tables inside the database should be monitored separately. This way, the web site owner is able to compare table-level sizes and to figure out which actual table increased in size more than expected.
Secondly, WPML is the only additional layer to your WordPress installation. Therefore it will behave differently on different WordPress websites.
Having this in mind, it’s not possible to understand the problem if you simply track a top-level issue, meaning the size of the database.
Finally, we need to track the size of each table separately. This way we understand how to properly optimize the WordPress database with WPML. Only then, we can locate the problem source.
Learn more about WPML tables and optimize WordPress database with WPML
WPML translates pages by creating copies of pages/posts etc.
Meaning, it’s expected to have increased size with every newly introduced language.
- The table that holds the language and translation information is called
icl_translations, so this table should be monitored for size changes.
- Strings related to the website are kept in the table icl_strings and their translations are kept inside the table
icl_string_translations. You should monitor these tables to be sure that the database size actually increased because of WPML.
You can find more information about the WPML database structure within the formal documentation. There is one great and detailed post about this.
Setup proper staging and test everything
It is not possible to guess what will happen to WPML database size when installed on a specific website, but for things like this, we do have staging areas.
It is highly recommended to create a staging website instance. Then install WPML and implement all multi-language settings there. Test everything and examine database sizes. After that, use speed and SQL query tests on both sites (original and staging with WPML).
With this, it is possible to determine existing issues. Remember, a proper understanding of the issue is usually 50% of the solution.
This is a better approach than simply assuming what will happen.
Use the WPML mechanism for database optimization
There are several quick steps to implement WPML database optimization:
- Always! Make a DB backup before database related operations
- Navigate to WPML > Support > Troubleshooting
- Select option Remove Ghost Entries
- Select option Use General Cleanup
Do proper research for your specific issue
This one seems like an elephant in the room, but it is usually poorly implemented or completely forgotten. Example of resolved issue: https://wpml.org/forums/topic/size-of-table-icl_string_pages/. This user had 400+ MB of string translations size, which is FIXED after updating to the latest version of the plugin.
Also, if you want to read about various blog posts related to WPML and recognized by WPML, you can visit this link.
Optimize MySQL Queries
If you want to optimize the WordPress database with WPML, this is a very important step.
Furthermore, this is used in cases where the translated page appears to be slower than its original. The complete tutorial can be found here https://wpml.org/faq/how-to-optimize-mysql-queries/
Long story short, you should consider two things:
- String Translation Tracking
- Go to WPML > String Translation.
- Disable option Track where strings appear on the site.
- Auto-ID Adjust
- Go to WPML > Languages > Make themes work multilingual.
- Disable option Adjust IDs for multilingual functionality.
How to optimize WordPress database in general
WordPress database optimization, in general, is a complex subject and it will be elaborated on in future posts (link will be available here).