Hi! Our website occasionally has slowdowns that result in 504s. Today I dug into the slow queries and found this particular one:
# Tables
# SHOW TABLE STATUS FROM `wp_ninjaone` LIKE 'wp_icl_translation_status'G
# SHOW CREATE TABLE `wp_ninjaone`.`wp_icl_translation_status`G
# SHOW TABLE STATUS FROM `wp_ninjaone` LIKE 'wp_icl_translations'G
# SHOW CREATE TABLE `wp_ninjaone`.`wp_icl_translations`G
# SHOW TABLE STATUS FROM `wp_ninjaone` LIKE 'wp_icl_translate_job'G
# SHOW CREATE TABLE `wp_ninjaone`.`wp_icl_translate_job`G
# SHOW TABLE STATUS FROM `wp_ninjaone` LIKE 'wp_posts'G
# SHOW CREATE TABLE `wp_ninjaone`.`wp_posts`G
# SHOW TABLE STATUS FROM `wp_ninjaone` LIKE 'wp_icl_languages'G
# SHOW CREATE TABLE `wp_ninjaone`.`wp_icl_languages`G
# SHOW TABLE STATUS FROM `wp_ninjaone` LIKE 'wp_icl_translation_batches'G
# SHOW CREATE TABLE `wp_ninjaone`.`wp_icl_translation_batches`G
# SHOW TABLE STATUS FROM `wp_ninjaone` LIKE 'wp_icl_string_packages'G
# SHOW CREATE TABLE `wp_ninjaone`.`wp_icl_string_packages`G
# SHOW TABLE STATUS FROM `wp_ninjaone` LIKE 'wp_icl_string_translations'G
# SHOW CREATE TABLE `wp_ninjaone`.`wp_icl_string_translations`G
# SHOW TABLE STATUS FROM `wp_ninjaone` LIKE 'wp_icl_strings'G
# SHOW CREATE TABLE `wp_ninjaone`.`wp_icl_strings`G
# SHOW TABLE STATUS FROM `wp_ninjaone` LIKE 'wp_icl_string_status'G
# SHOW CREATE TABLE `wp_ninjaone`.`wp_icl_string_status`G
# SHOW TABLE STATUS FROM `wp_ninjaone` LIKE 'wp_icl_core_status'G
# SHOW CREATE TABLE `wp_ninjaone`.`wp_icl_core_status`G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT (
SELECT
COUNT(translation_status.rid)
FROM wp_icl_translation_status translation_status
INNER JOIN wp_icl_translations translations
ON translations.translation_id = translation_status.translation_id INNER JOIN wp_icl_translations original_translations
ON original_translations.trid = translations.trid AND original_translations.language_code = translations.source_language_code INNER JOIN (
SELECT *
FROM wp_icl_translate_job as translate_job
WHERE job_id = (
SELECT MAX(job_id) AS job_id
FROM wp_icl_translate_job as sub_translate_job
WHERE sub_translate_job.rid = translate_job.rid
)
) AS translate_job ON translate_job.rid = translation_status.rid INNER JOIN wp_posts posts ON posts.ID = original_translations.element_id LEFT JOIN wp_icl_languages source_languages
ON source_languages.code = translations.source_language_code LEFT JOIN wp_icl_languages target_languages
ON target_languages.code = translations.language_code INNER JOIN wp_icl_translation_batches batches
ON batches.id = translation_status.batch_id WHERE original_translations.element_type LIKE 'post%' AND translation_status.status != '42' ) + (
SELECT
COUNT(translation_status.rid)
FROM wp_icl_translation_status translation_status
INNER JOIN wp_icl_translations translations
ON translations.translation_id = translation_status.translation_id INNER JOIN wp_icl_translations original_translations
ON original_translations.trid = translations.trid AND original_translations.language_code = translations.source_language_code INNER JOIN (
SELECT *
FROM wp_icl_translate_job as translate_job
WHERE job_id = (
SELECT MAX(job_id) AS job_id
FROM wp_icl_translate_job as sub_translate_job
WHERE sub_translate_job.rid = translate_job.rid
)
) AS translate_job ON translate_job.rid = translation_status.rid INNER JOIN wp_icl_string_packages string_packages
ON string_packages.ID = original_translations.element_id LEFT JOIN wp_icl_languages source_languages
ON source_languages.code = translations.source_language_code LEFT JOIN wp_icl_languages target_languages
ON target_languages.code = translations.language_code INNER JOIN wp_icl_translation_batches batches
ON batches.id = translation_status.batch_id WHERE original_translations.element_type LIKE 'package%' AND translation_status.status != '42' ) + (
SELECT
COUNT(string_translations.id)
FROM wp_icl_translation_batches AS translation_batches
INNER JOIN wp_icl_string_translations AS string_translations
ON string_translations.batch_id = translation_batches.id INNER JOIN wp_icl_strings AS strings
ON strings.id = string_translations.string_id LEFT JOIN wp_icl_string_status AS string_status
ON string_status.string_translation_id = string_translations.id LEFT JOIN wp_icl_core_status AS core_status
ON core_status.rid = string_status.rid LEFT JOIN wp_icl_languages source_languages
ON source_languages.code = strings.language LEFT JOIN wp_icl_languages target_languages
ON target_languages.code = string_translations.language INNER JOIN wp_icl_translation_batches batches
ON batches.id = string_translations.batch_id ) + (
SELECT
COUNT(translation_status.rid)
FROM wp_icl_translation_status translation_status
INNER JOIN wp_icl_translations translations
ON translations.translation_id = translation_status.translation_id INNER JOIN wp_icl_translations original_translations
ON original_translations.trid = translations.trid AND original_translations.language_code = translations.source_language_code INNER JOIN (
SELECT *
FROM wp_icl_translate_job as translate_job
WHERE job_id = (
SELECT MAX(job_id) AS job_id
FROM wp_icl_translate_job as sub_translate_job
WHERE sub_translate_job.rid = translate_job.rid
)
) AS translate_job ON translate_job.rid = translation_status.rid INNER JOIN wp_icl_translation_batches translation_batches ON translation_batches.id = original_translations.element_id LEFT JOIN wp_icl_languages source_languages
ON source_languages.code = translations.source_language_code LEFT JOIN wp_icl_languages target_languages
ON target_languages.code = translations.language_code INNER JOIN wp_icl_translation_batches batches
ON batches.id = translation_status.batch_id WHERE original_translations.element_type = 'st-batch_strings' AND translation_status.status != '42' ) /* From [www.ninjaone.com/wp-admin/admin.php?page=tm%2Fmenu%2Fmain.php&lang=en&admin_bar=1] in [/nas/content/live/ninjaone/wp-content/plugins/sitepress-multilingual-cms/classes/jobs/class-wpml-tm-jobs-repository.php:90] */G
Is there something we can do to prevent this query or to speed it up or make it less complex? Do you have any insight on anything we can do about this? Thank you!
