Why is it important to optimize WordPress database with WPML?
When we are considering e-commerce services, things with database size and web site speed gets very complicated. Very quickly we end up in handling large number of products, revisions of products, multiple number of images, comments etc.
These are only only some of the elements that influence web site speed and database optimization. Introducing multi-language mechanism as additional feature to e-commerce service (or online shop if you prefer better) creates additional layer of complexity to be handled.
This additional layer of complexity is the reason why most of a web site owners spend large amounts of time, countless efforts and think about various ideas to cope with optimizations. Let’s take, as an example, WordPress based web site, with WooCommerce shop, with 100+ products, several payment gateways and multi-language mechanism implemented with one of 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 premium and reliable product, but IT IS additional layer to already existing web site, so the quality of site alone will of course influence behavior of this (or any other) plugin.
However, web site owners and developer can optimize database. 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 to 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 WordPress database with WPML?
This process can be described in 7 simple steps:
- Update 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 setup proper JS and CSS minification to boost your web site performance.
1. Always update to latest version on WordPress core and WPML plugin
Database size issue is common issue with WPML installed on large scale web sites. This is resolved with latest versions of WPML plugin and sub-plugins.
Having that in mind, you should update all WPML related plugins to latest versions and manage maintain your WordPress web site in a right way.
This is indeed a common advice, but lots of web site owners won’t update their sites, either because of being afraid of bugs or because they don’t have licence any more.
2. 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 WordPress database with WPML.
Firstly, database is built with multiple tables. Furthermore each of these tables inside database should be monitored separately. This way, 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 only additional layer to your WordPress installation. Therefore it will behave differently on different WordPress web sites. Having this in mind, it’s not possible to understand problem if you simply track a top level issue (size of database).
Finally, we need to track size of each table separately to properly optimize WordPress database with WPML. Only then, we can locate the problem source.
3. Learn more about WPML tables (to be able to optimize WordPress database with WPML)
WPML does translation by creating copies of pages/posts etc. Meaning, it’s expected to have increased size on every new country (translation instance). 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 web site are kept in table icl_strings and their translations are kept inside table icl_string_translations. You should monitor these tables to be sure that database size actually increased because of WPML.
4. Setup proper staging and test everything before implementing to production
It is not possible to guess what will happen to WPML database size when installed on specific web site, but for things like this we do have staging areas.
It is highly recommended to create staging web site 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, proper understanding of issue is usually 50% of solution.
This is better approach then simply assuming what will happen.
5. Use 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
6. Do a 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 plugin.
Also, if you want to read about various blog posts related to WPML and recognized by WPML, you can visit this link.
7. Optimize MySQL Queries
If you want to optimize WordPress database with WPML, this is a very important step.
Furthermore, this is used in a cases where translated page appears to be slower than its original. 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 complex subject and it will be elaborated in future posts (link will be available here).
Feel free to leave your comments, experiences and questions related to WPML and database optimization in general. Finally, spread the word! Like, Share, Comment!