WPML is not open source. But I have obtained a dev license.

I can provide you a license key if you like.

pixelBrain -- Can you provide me with SHOW CREATE TABLE for the tables and some sample queries?  (I can probably get started faster with simply that info.) Rick Jamesemail: @.*** On Friday, June 3, 2022, 02:01:29 PM PDT, pixelBrain @.> wrote: I can provide you a license key if you like. — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you are subscribed to this thread.Message ID: @.>

I did a quick "Monitor" and pulled out these slowest queries. Hopefully this could help you with a starting point:

SELECT p.ID FROM wp_posts p LEFT JOIN wp_icl_translations i ON CONCAT(s, p.post_type ) = i.element_type AND i.element_id = p.ID WHERE p.post_type = s AND i.language_code IS NULL

SELECT wpml_translations.element_id, tax.term_id, tax.taxonomy FROM wp_icl_translations wpml_translations JOIN wp_term_taxonomy tax ON wpml_translations.element_id = tax.term_taxonomy_id AND wpml_translations.element_type = CONCAT(s, tax.taxonomy) JOIN wp_terms terms ON terms.term_id = tax.term_id WHERE tax.term_id != tax.term_taxonomy_id

SELECT wpml_translations.translation_id, wpml_translations.language_code, wpml_translations.element_id, wpml_translations.source_language_code, wpml_translations.element_type, NULLIF(wpml_translations.source_language_code, s) IS NULL AS original , tm.name, tm.term_id, COUNT(tr.object_id) AS instances FROM wp_icl_translations wpml_translations LEFT JOIN wp_term_taxonomy tt ON wpml_translations.element_id=tt.term_taxonomy_id LEFT JOIN wp_terms tm ON tt.term_id = tm.term_id LEFT JOIN wp_term_relationships tr ON tr.term_taxonomy_id=tt.term_taxonomy_id WHERE 1 AND wpml_translations.trid=i GROUP BY tm.term_id

SELECT wpml_translations.translation_id, wpml_translations.element_id, wpml_translations.language_code, wpml_translations.source_language_code, wpml_translations.trid, wpml_translations.element_type FROM wp_icl_translations wpml_translations JOIN wp_term_taxonomy tax ON wpml_translations.element_id = tax.term_taxonomy_id AND wpml_translations.element_type = CONCAT(s, tax.taxonomy) JOIN wp_icl_translations tridt ON tridt.element_type = wpml_translations.element_type AND tridt.trid = wpml_translations.trid WHERE tridt.trid = (SELECT trid FROM wp_icl_translations wpml_translations JOIN wp_term_taxonomy tax ON wpml_translations.element_id = tax.term_taxonomy_id AND wpml_translations.element_type = CONCAT(s, tax.taxonomy) WHERE element_id = i LIMIT 1)

These arent all the tables, but it's the tables that had the most rows and/or largest in my db:

CREATE TABLE wp_icl_translate(tidbigint(20) unsigned NOT NULL AUTO_INCREMENT,job_idbigint(20) unsigned NOT NULL,content_idbigint(20) unsigned NOT NULL,timestamptimestamp NOT NULL DEFAULT current_timestamp(),field_typevarchar(160) COLLATE utf8mb4_unicode_ci NOT NULL,field_wrap_tagvarchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,field_formatvarchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,field_translatetinyint(4) NOT NULL,field_datalongtext COLLATE utf8mb4_unicode_ci NOT NULL,field_data_translatedlongtext COLLATE utf8mb4_unicode_ci NOT NULL,field_finished tinyint(4) NOT NULL DEFAULT 0, PRIMARY KEY (tid), KEY job_id (job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1271621 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE wp_icl_translation_status (
rid bigint(20) NOT NULL AUTO_INCREMENT,
translation_id bigint(20) NOT NULL,
status tinyint(4) NOT NULL,
translator_id bigint(20) NOT NULL,
needs_update tinyint(4) NOT NULL,
md5 varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
translation_service varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
translation_package longtext COLLATE utf8mb4_unicode_ci NOT NULL,
timestamp timestamp NOT NULL DEFAULT current_timestamp(),
links_fixed tinyint(4) NOT NULL DEFAULT 0,
_prevstate longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
batch_id int(11) NOT NULL DEFAULT 0,
uuid varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
tp_id int(11) DEFAULT NULL,
tp_revision int(11) NOT NULL DEFAULT 1,
ts_status text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
review_status enum('NEEDS_REVIEW','EDITING','ACCEPTED') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
ate_comm_retry_count int(11) unsigned DEFAULT 0,
PRIMARY KEY (rid),
UNIQUE KEY translation_id (translation_id)
) ENGINE=InnoDB AUTO_INCREMENT=1104345 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
CREATE TABLE wp_icl_translations (
translation_id bigint(20) NOT NULL AUTO_INCREMENT,
element_type varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post_post',
element_id bigint(20) DEFAULT NULL,
trid bigint(20) NOT NULL,
language_code varchar(7) COLLATE utf8mb4_unicode_ci NOT NULL,
source_language_code varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (translation_id),
UNIQUE KEY trid_lang (trid,language_code),
UNIQUE KEY el_type_id (element_type,element_id),
KEY id_type_language (element_id,element_type,language_code)
) ENGINE=InnoDB AUTO_INCREMENT=2000609 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE wp_icl_strings (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
language varchar(7) COLLATE utf8mb4_unicode_ci NOT NULL,
context varchar(160) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
name varchar(160) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
value longtext COLLATE utf8mb4_unicode_ci NOT NULL,
string_package_id bigint(20) unsigned DEFAULT NULL,
type varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'LINE',
title varchar(160) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
status tinyint(4) NOT NULL,
gettext_context mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
domain_name_context_md5 varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
location bigint(20) unsigned DEFAULT NULL,
wrap_tag varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
word_count int(10) unsigned DEFAULT NULL,
translation_priority varchar(160) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uc_domain_name_context_md5 (domain_name_context_md5) USING BTREE,
KEY language_context (language,context),
KEY string_package_id (string_package_id),
KEY icl_strings_name (name),
KEY icl_strings_translation_priority (translation_priority),
KEY context (context)
) ENGINE=InnoDB AUTO_INCREMENT=1670009 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE wp_icl_string_positions (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
string_id bigint(20) NOT NULL,
kind tinyint(4) DEFAULT NULL,
position_in_page varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (id),
KEY string_id (string_id)
) ENGINE=InnoDB AUTO_INCREMENT=38105 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE wp_icl_string_translations (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
string_id bigint(20) unsigned NOT NULL,
language varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
status tinyint(4) NOT NULL,
value longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
mo_string longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
translator_id bigint(20) unsigned DEFAULT NULL,
translation_date timestamp NOT NULL DEFAULT current_timestamp(),
batch_id int(11) NOT NULL DEFAULT 0,
translation_service varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (id),
UNIQUE KEY string_language (string_id,language),
KEY status (status)
) ENGINE=InnoDB AUTO_INCREMENT=1638251 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE wp_icl_translate_job (
job_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
rid bigint(20) unsigned NOT NULL,
translator_id int(10) unsigned NOT NULL,
translated tinyint(3) unsigned NOT NULL DEFAULT 0,
manager_id int(10) unsigned NOT NULL,
revision int(10) unsigned DEFAULT NULL,
title varchar(160) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
deadline_date datetime DEFAULT NULL,
completed_date datetime DEFAULT NULL,
editor varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
editor_job_id bigint(20) unsigned DEFAULT NULL,
automatic tinyint(3) unsigned NOT NULL DEFAULT 0,
ate_sync_count int(6) unsigned DEFAULT 0,
PRIMARY KEY (job_id),
KEY rid (rid,translator_id)
) ENGINE=InnoDB AUTO_INCREMENT=32594 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE wp_icl_string_status (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
rid bigint(20) NOT NULL,
string_translation_id bigint(20) NOT NULL,
timestamp timestamp NOT NULL DEFAULT current_timestamp(),
md5 varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (id),
KEY string_translation_id (string_translation_id)
) ENGINE=InnoDB AUTO_INCREMENT=3214 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE wp_icl_languages_translations (
id int(11) NOT NULL AUTO_INCREMENT,
language_code varchar(7) COLLATE utf8mb4_unicode_ci NOT NULL,
display_language_code varchar(7) COLLATE utf8mb4_unicode_ci NOT NULL,
name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY language_code (language_code,display_language_code)
) ENGINE=InnoDB AUTO_INCREMENT=5597 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE wp_icl_string_batches (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
string_id bigint(20) unsigned NOT NULL,
batch_id bigint(20) unsigned NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=7459 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

`

Thanks, im definitely out of my depth here on optimizing the queries. Maybe someone else will know more.

Thanks for this info, @tridoan-px @tridoan @rjasdf .

It looks like the wp_icl_translations table is performance-critical, judging from the three queries you provided. The indexes on there are not bad. No prefix indexes. Some seemingly appropriate multicolumn indexes. There may be other slow queries that use other tables, of course. It looks like many of these table relate to WPML's translation-service-bureau business rather than to presenting localized WordPress pages to the audience.

It's hard to know more without actual data to use with EXPLAIN ANALYZE statements. The sample definition you provided for that table shows it's large at a couple of million rows.

There may be an opportunity to change the primary key from the autoincrementing translation_id to a multicolumn compound key involving the element_type and element_id columns, maybe with language_code in it. That would get us the clustered-index advantage for lookups involving those columns, which might make a difference.

@tridoan is there any chance you can provide dumps of these tables for us to use in analysis? (I know you might have to answer "no" -- your data is, of course, yours.)

wp_icl_translations
wp_posts
wp_term_taxonomy
wp_term_relationships
wp_terms

Here are a couple of query monitors and the SQL defining a WordPress + WPML database. They're from an absurdly small WPML site, so query-planner index choices are distorted. But the monitors do show queries like the one in this issue.

One monitor is WITH the high-perf keys and the other without. The SQL is with the high-perf keys.

Notice that the site in question has a persistent object cache; that gets rid of a lot of chaff queries to and from the wp_options table.

VerySmallWPMLSiteSQLandMonitors.zip

(The WPML folks were kind enough to grant me a dev license.)

hi @OllieJones , is it possible we can chat offline? I can give you some access if needed, but am unable to publicly provide here. Sorry for the delay, been pulled too many ways the past week

Sure, please sent me an email . Address on my github profile.

(I've been away on vac. back in the saddle now.)

Any update on optimizing wpml ? :)

Any update on optimizing wpml ? :)

@ufo56 - Are you willing to test the indexes I supplied above?

Yes, of course. WPML String Translation mysql query performance ( wp_icl_strings) sucks so much that i dont have words.

EDIT: If you need also need whatever testing environment, i can give you. I have here 90k product shop with 2 languages, slowest component is WPML

WPML Strings Translation ON https://i.imgur.com/UMqg3ws.png
WPML Strings Translation OFF https://i.imgur.com/5yl9Qdq.png

ALTER TABLE wp_icl_translations ADD PRIMARY KEY(translation_id, element_type, element_id);

#1265 - Data truncated for column 'element_id' at row 770

No primary keys added

Ugh! Do you happen to know the values of those 3 columns for the 770th row? I see that two are BIGINTs and one is VARCHAR(36); did any of those datatypes change?
I am willing to take the dump; I will work only with the data; not WP/WPML. I should be able to figure out the ALTER error (though it is baffling so far).

Ugh! Do you happen to know the values of those 3 columns for the 770th row? I see that two are BIGINTs and one is VARCHAR(36); did any of those datatypes change? I am willing to take the dump; I will work only with the data; not WP/WPML. I should be able to figure out the ALTER error (though it is baffling so far).

EDIT: There are rows where element_id is NULL, seems that is the problem here. Removed rows with NULL element_id and now it worked

Further research about WPML suggests that using a persistent object cache plugin will help performance with WPML. Their developers went to a lot of trouble to cache results of SQL queries.

Unfortunately there are many issues with object caches in combination with WPML. We tried multiple of them and always had problems, such as showing old translations or 404 redirects on translated pages.