index-wp-mysql-for-speed icon indicating copy to clipboard operation
index-wp-mysql-for-speed copied to clipboard

Published 20 hours ago • verified publisher icon OllieJones

WPML plugin: see if we can make its tables any faster.

Open OllieJones opened this issue 3 years ago • 20 comments

Can we look at the WPML plugin's indexes to find opportunities for speedups? https://wpml.org/

Per this request. https://wordpress.org/support/topic/please-add-wpml-support/

OllieJones avatar Jun 03 '22 10:06 OllieJones

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

OllieJones avatar Jun 03 '22 10:06 OllieJones

I can provide you a license key if you like.

tridoan avatar Jun 03 '22 21:06 tridoan

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: @.***>

rjasdf avatar Jun 03 '22 21:06 rjasdf

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)

tridoan avatar Jun 03 '22 21:06 tridoan

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

`

tridoan avatar Jun 03 '22 22:06 tridoan

Thanks.  Here's a first pass: Without seeing the CREATE TABLEs, My index suggestions may be redundant. I assume "s" and "i" are the digests for literal "string" and "integer"? Skip to the end to see the summary of index suggestions.Some coding suggestions are interspersed.

Query 1: Is this looking for all missing entry in wp_icl_translations?  What will ID be used for then?     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  Index recommendations (In this case, they are both "composite" and "covering"):     wp_posts:  INDEX(post_type,  ID)    wp_icl_translations:  INDEX(element_type,  element_id, language_code)    When adding a composite index, DROP index(es) with the same leading columns.That is, when you have both INDEX(a) and INDEX(a,b), toss the former. Re: CONCAT:  In general, an expression in ON may be problematical.  (It may not be a problem in this particular case.)

Query 2:  (I took the liberty of shortening an alias.)     SELECT  i.element_id, tax.term_id, tax.taxonomy        FROM  wp_icl_translations i        JOIN  wp_term_taxonomy tax            ON  i.element_id = tax.term_taxonomy_id          AND  i.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      wp_icl_translations:  INDEX(element_type,  element_id)    tax:  INDEX(term_id, term_taxonomy_id,  taxonomy)    terms:  INDEX(term_id)    Is term_id the PRIMARY KEY for terms?  If so, then do not add INDEX(term_id) it would be redundant and wasteful. You may be able to remove     JOIN  wp_terms terms  ON  terms.term_id = tax.term_id No other uses for that table are made.  Its only purpose is to verify that there is a matching row in that table. Removing that JOIN is likely to speed up the query. ----------------------------------------Query 3:     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      wpml_translations:  INDEX(trid)    wp_terms:  INDEX(term_id,  name)   -- unless it has PRIMARY KEY(term_id)    wp_term_taxonomy:  INDEX(term_taxonomy_id,  object_id)    tt:  INDEX(term_taxonomy_id,  term_id) This query may be formulated wrong.  It is a common problem -- in both computation (the COUNT) and in performance:

  1. JOIN several tables -- this builds a temp table with all the valid combinations.2. Do the aggregates (such as COUNT) on  the large temp table.3. Shrink the results using GROUP BY. Please verify that the COUNTs look correct.  Also tell me more about the LEFT JOINs -- are they typically 0 or 1, or 0 or many maybe they should be 1 to many. Have you tested this with MySQL 8.0?  Cf:  "ONLY_FULL_GROUP_BY". I will look more closely after you have provided some answers. Is wp_terms a simple lookup table that maps id <=> name? Are tr and tt many-to-many mapping tables?  There is a common performance issue in such; I need to see SHOW CREATE TABLE to verify. "NULLIF(wpml_translations.source_language_code, s) IS NULL AS original" -- seems a bit contorted; what is the intent?  Maybe I can simplify it. ----------------------------------------Query 4:     SELECT  i.translation_id, i.element_id,            i.language_code, wpml_translations.source_language_code,            i.trid, i.element_type        FROM  wp_icl_translations i        JOIN  wp_term_taxonomy tax  ON i.element_id = tax.term_taxonomy_id          AND  i.element_type = CONCAT(s, tax.taxonomy)        JOIN  wp_icl_translations tridt  ON tridt.element_type = i.element_type          AND  tridt.trid = i.trid        WHERE  tridt.trid =         (            SELECT  trid                FROM  wp_icl_translations i2                JOIN  wp_term_taxonomy tax  ON i2.element_id = tax.term_taxonomy_id                  AND  i2.element_type = CONCAT(s, tax.taxonomy)                WHERE  i2.element_id = i                LIMIT  1        )      tax:  INDEX(term_taxonomy_id,  taxonomy)    wp_icl_translations:  INDEX(trid, element_type)    wp_icl_translations:  INDEX(element_id,  element_type) ----------------------------------------    Summary of new indexes (eliminating dups, etc)     wp_term_taxonomy:  -- nothing new if term_taxonomy_id is the PK     wp_icl_translations:  INDEX(element_type,  element_id, language_code)    wp_icl_translations:  INDEX(trid, element_type)     wp_posts:  INDEX(post_type,  ID)     wp_terms:  INDEX(term_id,  name)   -- unless it has PRIMARY KEY(term_id)

FROM wp_icl_translations wpml_translations(This naming is confusing; I hope I did not mess things up.)

Rick Jamesemail: @.***

On Friday, June 3, 2022, 02:45:56 PM PDT, pixelBrain ***@***.***> wrote:

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)

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.Message ID: @.***>

rjasdf avatar Jun 03 '22 22:06 rjasdf

Thanks.translation_id is UNIQUE in one table and PRIMARY in another.  That sort of means they could be merged into a single table.  Thoughts on that?

As with WP, BIGINT is overkill for many numeric fields.  It occupies 8 bytes.  INT UNSIGNED takes 4 bytes but has a limit of 4 billion. UUIDs could be compressed into BINARY(16) (16 bytes) instead of VARCHAR(36).  However, the code for such is different between MySQL and MariaDB, so it may be unwise to use the builtin techniques. Whenever there is both a PRIMARY KEY and a UNIQUE, I wonder if the PK could be removed.  (This may not be worth pursuing.)  I'm thinking especially of rid/trid/translation_id. Inconsistent:  language vs language_code, VARCHAR(10) vs (7).  (Not serious.) The indexes are mostly in good shape. Where I mentioned "element_type,  element_id", the order of those two probably does not matter.  Hence, my suggestions on wp_icl_translations are already in place.

Rick Jamesemail: @.***

On Friday, June 3, 2022, 03:15:46 PM PDT, pixelBrain ***@***.***> wrote:

Also:

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

`

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.Message ID: @.***>

rjasdf avatar Jun 03 '22 23:06 rjasdf

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

tridoan-px avatar Jun 04 '22 06:06 tridoan-px

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

OllieJones avatar Jun 06 '22 14:06 OllieJones

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.)

OllieJones avatar Jun 08 '22 11:06 OllieJones

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

tridoan avatar Jun 16 '22 16:06 tridoan

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

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

OllieJones avatar Jun 23 '22 18:06 OllieJones

Any update on optimizing wpml ? :)

ufo56 avatar Aug 19 '22 17:08 ufo56

Any update on optimizing wpml ? :)

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

rjasdf avatar Aug 19 '22 19:08 rjasdf

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

ufo56 avatar Aug 19 '22 19:08 ufo56

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

ufo56 avatar Aug 19 '22 20:08 ufo56

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).

rjasdf avatar Aug 20 '22 00:08 rjasdf

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

ufo56 avatar Aug 20 '22 09:08 ufo56

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.

OllieJones avatar Oct 31 '23 16:10 OllieJones

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.

cbratschi avatar Oct 31 '23 17:10 cbratschi