Repetitive Database Query Affecting Performance

I see this query running on another site. It even more jumbled.
The query needs to be fixed. Its left joining to the postmeta without meta_key(in the example I have). It does it down in the where clause. And the joins are multiple times to the same meta key. Why?

SELECT
wp_posts.ID
FROM
wp_posts
LEFT JOIN wp_term_relationships
ON (
wp_posts.ID = wp_term_relationships.object_id
)
LEFT JOIN wp_postmeta
ON (
wp_posts.ID = wp_postmeta.post_id
AND wp_postmeta.meta_key = '_wc_gla_visibility'
)
LEFT JOIN wp_postmeta AS mt1
ON (wp_posts.ID = mt1.post_id)
LEFT JOIN wp_postmeta AS mt2
ON (
wp_posts.ID = mt2.post_id
AND mt2.meta_key = '_wc_gla_errors'
)
LEFT JOIN wp_postmeta AS mt3
ON (wp_posts.ID = mt3.post_id)
LEFT JOIN wp_postmeta AS mt4
ON (wp_posts.ID = mt4.post_id)
LEFT JOIN wp_postmeta AS mt5
ON (
wp_posts.ID = mt5.post_id
AND mt5.meta_key = '_wc_gla_visibility'
)
LEFT JOIN wp_postmeta AS mt6
ON (wp_posts.ID = mt6.post_id)
LEFT JOIN wp_postmeta AS mt7
ON (
wp_posts.ID = mt7.post_id
AND mt7.meta_key = '_wc_gla_errors'
)
LEFT JOIN wp_postmeta AS mt8
ON (wp_posts.ID = mt8.post_id)
LEFT JOIN wp_postmeta AS mt9
ON (wp_posts.ID = mt9.post_id)
WHERE 1 = 1
AND (
(
wp_term_relationships.term_taxonomy_id IN (2, 4)
OR NOT EXISTS
(SELECT
1
FROM
wp_term_relationships
INNER JOIN wp_term_taxonomy
ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
WHERE wp_term_taxonomy.taxonomy = 'product_type'
AND wp_term_relationships.object_id = wp_posts.ID)
)
)
AND (
(
wp_postmeta.post_id IS NULL
OR (
mt1.meta_key = '_wc_gla_visibility'
AND mt1.meta_value != 'dont-sync-and-show'
)
)
AND (
mt2.post_id IS NULL
OR (
mt3.meta_key = '_wc_gla_errors'
AND mt3.meta_value = ''
)
)
AND (
(
mt4.meta_key = '_wc_gla_synced_at'
AND mt4.meta_value < '1740035105'
)
)
AND (
mt5.post_id IS NULL
OR (
mt6.meta_key = '_wc_gla_visibility'
AND mt6.meta_value != 'dont-sync-and-show'
)
)
AND (
mt7.post_id IS NULL
OR (
mt8.meta_key = '_wc_gla_errors'
AND mt8.meta_value = ''
)
)
AND (
(
mt9.meta_key = '_wc_gla_synced_at'
AND mt9.meta_value < '1740035105'
)
)
)
AND wp_posts.post_type IN ('product', 'product_variation')
AND (
(
wp_posts.post_status IN( 'publish','draft', 'pending', 'private')
)
)
GROUP BY wp_posts.ID
limit 300, 100

Here is my first pass, but it can be reduced farther. 


SELECT DISTINCT
wp_posts.ID
FROM
wp_posts
LEFT JOIN wp_term_relationships
ON (
wp_posts.ID = wp_term_relationships.object_id
)
LEFT JOIN wp_postmeta mt1
ON (
wp_posts.ID = mt1.post_id
AND mt1.meta_key = '_wc_gla_visibility'
)
LEFT JOIN wp_postmeta AS mt2
ON (wp_posts.ID = mt2.post_id)
AND mt2.meta_key = '_wc_gla_errors'
LEFT JOIN wp_postmeta AS mt3
ON (
wp_posts.ID = mt3.post_id
AND mt3.meta_key = '_wc_gla_synced_at'
)
WHERE 1 = 1
AND wp_posts.post_type IN ('product', 'product_variation')
AND (
(
wp_posts.post_status IN( 'publish','draft', 'pending', 'private')
)
)
AND (
(
wp_term_relationships.term_taxonomy_id IN (2, 4)
OR NOT EXISTS
(SELECT
1
FROM
wp_term_relationships
INNER JOIN wp_term_taxonomy
ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
WHERE wp_term_taxonomy.taxonomy = 'product_type'
AND wp_term_relationships.object_id = wp_posts.ID)
)
)
AND (
(
mt1.post_id IS NULL
OR (
IFNULL(mt1.meta_value,'') != 'dont-sync-and-show'
)
)
AND (
mt2.post_id IS NULL
OR (
mt2.meta_value = ''
)
)
AND (
(
IFNULL(mt3.meta_value,'0') < '1740035105'
)
)
)

Hello pmathbliss,

Thank you for your message and I appreciate your contribution to this forum.

The suggestions you shared are very helpful. Our developers are looking into the information shared by the thread starter and will post an update here when ready.

If you need assistance, feel free to create a new topic.

Have a great day!