When e-commerce services are considered, things with database size and web site speed tend to get very complicated. Large number of products, revisions of products, multiple number of images, comments etc., are 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, there are things web site owners and developers CAN DO to optimize database and to 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 WPML plugin database
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, and they are trying to resolve it 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, but information that database size increased is not enough.
Database is built with multiple tables, and 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.
As mentioned before, WPML is only additional layer to your WordPress installation, and 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).
We need to track size of each table separately, to be are aware of the problem source.
3. Learn more about WPML tables (for developers)
WPML does translation by creating copies of pages/posts etc. so 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.
There is one great and detailed post about this: https://wpml.org/documentation/support/wpml-tables/
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, install WPML and implement all multi-language settings there and examine database sizes. After that, using speed and SQL query tests on both sites (original and staging with WPML) 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/ , 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: https://wpml.org/home/wpml-on-the-web/ (this post is there too..)
7. Optimize MySQL Queries
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, there are two things to consider:
- 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
Optimization of WordPress database in general is complex subject and it will be elaborated in future posts (link will be available here).Meanwhile, you can subscribe to our knowledge base to be notified when important posts such as this one are published.
Feel free to leave your comments, experiences and questions related to WPML and database optimization in general. Finally, spread the word! Like, Share, Comment!
We put a lot of effort in providing these free technical tutorials and we want it to reach the right audience! 🙂