pmathbliss

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