Background of the issue:
We are running a WooCommerce store with WPML active on a dedicated VPS (32GB RAM, SSD, Redis, PHP 8.1). The frontend performance is fine, but the backend is extremely slow when managing products (adding, editing, bulk editing). We have already performed extensive server-level tuning (MySQL buffer pool optimization, Redis cache, Opcache, PHP memory limits). The server has plenty of available resources (CPU/RAM are not the bottleneck). We enabled the MySQL slow query log and identified that many of the slowest queries originate from WPML + WooCommerce. Specifically: CREATE TEMPORARY TABLE … SELECT … JOIN queries that join wp_posts, wp_postmeta, wp_terms, and icl_translations. These queries take 10–15 seconds to execute and examine tens of thousands of rows. Queries involving icl_translations when filtering or searching products in the backend. Sample queries are available in our log file (we can provide them if needed). WooCommerce store with thousands of products and variations. WPML for translations (Greek/English). Server stack: LiteSpeed, MariaDB 10.x, PHP 8.1, Redis, 32GB RAM. MySQL slow query log shows WPML queries as some of the top bottlenecks. This is a business-critical e-shop and the backend slowness affects our ability to manage products efficiently.
Symptoms:
The backend is extremely slow when managing products due to heavy SQL queries involving WPML and WooCommerce.
Questions:
Is this a known issue with WPML in WooCommerce backends with large catalogs?
Are there recommended indexes for icl_translations or other WPML-related tables that reduce query execution time?
Are there best practices for optimizing WPML performance in WooCommerce product management (e.g. disabling certain filters, cleaning up unused translations, optimizing queries)?
Does WPML provide a documented approach or patch for improving backend performance when product counts and metadata grow large?
The main problem is about the slow backend due to heavy SQL queries...
MySQL slow query log shows WPML queries as some of the top bottlenecks... i wish to share with your team my log files and help me solve the MySQL speed problem.
1) That’s correct, but in your last chat message, you shared the error message, so I’m providing a fix/workaround for the reported REST API issue.
2) Regarding the SQL issue — we’ll be glad to help investigate further.
To proceed, SQL logs would be very helpful.
In the meantime, please try the following steps and let me know if they improve the situation:
- Ensure you have a backup.
- Disable all plugins except WPML plugins and WooCommerce.
- Switch to the default WP theme or the Flatsome parent theme.
- Check if the issue persists.
Please let me know the results after testing and if that helps or not, it would be helpful for further debugging.
Hello Drazen,
Thank you for your reply.
I would like to clarify the situation so we can move forward more efficiently:
We have already worked closely with our hosting provider’s senior sysadmins. They have tuned the server at MySQL and system level (buffer pool, Redis, PHP memory, etc.) and enabled the MySQL slow query log.
We collected detailed logs and also ran EXPLAIN on the slowest queries. These consistently show that the bottleneck comes from complex joins between WooCommerce and WPML tables (posts, postmeta, term_relationships, icl_translations).
Some of these queries take 10–15 seconds to complete, even though the server has plenty of resources available (32GB RAM, SSD, Redis, LiteSpeed, PHP 8.1).
The sysadmins suggested possible composite indexes to improve performance (for example on bnc_term_relationships(term_taxonomy_id, object_id), bnc_postmeta(meta_key, meta_value, post_id), wpml_translations(element_id, element_type), bnc_posts(post_type, post_status, ID)). However, they cannot apply these changes, as this requires application-level (WPML/WooCommerce) expertise.
What I need from WPML
At this stage, generic plugin conflict tests (disabling all plugins/themes) are not enough. We have already done this part. We already know the issue is specifically with WPML queries in WooCommerce backend product management.
What I need is to escalate this case to a specialized WPML technical engineer who can:
1. Review the slow query log and EXPLAIN outputs we already have.
2. Confirm whether the suggested indexes are correct and safe to apply.
3. Provide WPML-recommended best practices or schema optimizations for large WooCommerce stores.
4. Advise if WPML has known solutions or patches for heavy queries in WooCommerce product management.
This is a business-critical WooCommerce shop and backend slowness is heavily impacting our daily work. I kindly request escalation to a senior WPML technician with MySQL/WooCommerce expertise so we can move toward a proper solution.
Please let me know how I can securely share the slow query logs and EXPLAIN output with you for review.
I completely understand, and we’ll be glad to help. There are just a few steps we need to go through before providing any meaningful advice.
Sharing SQL logs can be helpful, but sometimes it’s difficult to draw clear conclusions from them alone. For example, certain queries may appear slower if there are 5,000 custom fields in a post — just to give one scenario.
If the issue still occurs in the minimal environment as suggested, the best next step would be to share access to a staging site or a Duplicator package. That way, we can properly check the site, investigate how and why the SQL logs are generated, and provide accurate guidance. If it turns out to be a WPML bug or unexpected behavior, we can escalate it further.
You can share link to SQL logs and staging site details / Duplicator package in your next private reply.
Thank you for your reply and for confirming the next steps. I fully understand the need for a staging or backup package for deeper analysis.
To summarize briefly:
We have already collected slow query logs and EXPLAIN outputs with the help of our sysadmins. These clearly show that the heaviest queries come from complex joins between WooCommerce tables and WPML’s icl_translations. Some queries take 10–15 seconds even though the server resources are more than sufficient.
Our hosting provider suggested possible indexes, but since this involves WPML’s schema and query design, we need your guidance.
I can prepare a fresh full backup of the site using WPVivid Backup plugin (files + database). Could you please confirm if your team can work with a WPVivid backup package, or if you specifically require Duplicator?
Also, just to clarify: when you restore this package on your side, I assume you will set it up locally or on your testing servers, so it will not be connected to our live traffic or production environment. Please confirm this understanding.
Once I know which format you prefer, I will generate a fresh backup and share it with you privately together with a sample of the slow query logs and EXPLAIN outputs.
1) If possible, we would prefer Duplicator package. When creating package, you can also select file filters and exclude any large files, like backups and etc to keep the package size low..
2) Yes, we would be checking on our side / local host and it will not be connected to your live website.
Thank you for confirming that the package will be restored on your local/test environment and not connected to our live website.
However, please note that to exclude large files we need Pro version hidden link we will not purchase a premium plugin license just to provide you with a Duplicator package.
Our company already uses WPVivid Backup, which is a reliable and free plugin, fully capable of generating complete site + database backups.
It is not reasonable to ask us to buy a commercial plugin simply to share a backup, when a standard and widely accepted alternative is already available.
Therefore, we kindly request that you accept a WPVivid full backup package, which we can prepare and share with you. If for any reason WPML support cannot work with WPVivid packages, then we would need this case escalated to a senior technical engineer who can.
Of course, you will not buy a plugin only to provide us a package, that was not my intention, I was not aware this is part of premium feature.
You can use free Duplicator package, if it fails for any reasons sure please proceed with WP Vivid Backup. The reason why we prefer Duplicator is because it is easier to deploy such package, rather than to do it manually via ZIP and DB imports that can fail on different environments.
I have enabled private reply, so when it is ready feel free to share links.
Thank you for your clarification.
I have prepared a full WPVivid backup of the site (files + database), which I will share with you in a private reply shortly. Please restore and review it on your local/test environment as discussed.
I have uploaded the materials you need in order to fully reproduce and investigate the issue:
Two full WPVivid backups (files + database) — available here: hidden link
These are full backups and can be restored using the following instructions: hidden link
MySQL report (.rar) — this contains all MySQL slow query logs collected by our sysadmins. This is the primary material for analysis and is included in the same Google Drive folder.
Important notes:
These backups are provided for use only on your local/test environment. The production site must not be touched.
WPVivid backups restore exactly like Duplicator; you simply need to use your own DB credentials during the restore.
Problem recap
The backend of the site (adding/editing products) is extremely slow.
This is not due to lack of server resources: the VPS has 32GB RAM, SSD storage, Redis, and sufficient CPU/IO.
The most expensive queries come from complex joins between WooCommerce and WPML (posts, postmeta, term_relationships, icl_translations) with execution times of 10–15 seconds.
Our hosting provider already ran EXPLAIN and suggested specific composite indexes as possible improvements (e.g. (term_taxonomy_id, object_id) on term_relationships, (meta_key, meta_value, post_id) on postmeta, (element_id, element_type) on wpml_translations, (post_type, post_status, ID) on posts).
What I expect from WPML support
Please work with the provided backup and logs, and return concrete technical outputs — not general advice:
Restore the site locally using the WPVivid backup.
Review the included MySQL report (.rar) to understand which slow queries are logged.
Provide a detailed technical report containing:
a. The 20 slowest queries (full SQL).
b. The EXPLAIN/ANALYZE output for each query.
c. Specific index recommendations, including the exact SQL commands (ALTER TABLE … ADD INDEX …).
d. A risk/impact assessment for each proposed change (e.g. potential table lock time, downtime requirements, rollback plan).
e. Confirmation whether these queries are caused by a known WPML limitation or bug, and whether an official patch or workaround exists.
If feasible, provide a safe migration plan for applying these indexes on staging first (backup → apply indexes → validate → rollback plan). If not, at least provide us with the SQL so we can apply and test on staging.
If deeper investigation suggests a WPML core bug, please escalate this case to senior WPML engineers with a clear timeline.
Deliverables expected
slow_queries_top20.sql — the actual queries.
explain_top20.txt/json — the EXPLAIN results.
proposed_indexes.sql — the list of recommended indexes.
short_report.pdf/txt — summary, risk analysis, and recommended next steps.
Final remarks
Please confirm you have successfully downloaded the backups and logs, and let me know when you will begin the restore and analysis.
We have already worked closely with our hosting sysadmins, and their EXPLAIN analysis points strongly towards WPML-related queries. Therefore, I kindly ask you not to return with generic advice such as disabling plugins or switching themes — we have already tested those scenarios extensively.
Thank you for your cooperation and I look forward to receiving a clear, technical, and actionable response.
Thank you for getting back to me. I’ve requested approval to access the files and will proceed once it’s granted.
As mentioned, we’ll do our best to confirm whether the issue is related to WPML and suggest an appropriate solution. After identifying the cause and how it occurs, we’ll determine if it’s a WPML bug, if improvements can be suggested, or if it’s related to something else. As we do have some known performance issues that we are working on permeant fix, for example with Yoast SEO etc.
Please note that I first need to debug and confirm the issue is not caused by anything specific outside WPML before I can escalate a confirmed issue or bug.
Once access to the files is approved, I’ll deploy, investigate, and continue further. I’ll keep you updated on my findings and the next steps.
