CODEWITHSUNDEEP
mysqlwoocommerce
eozzy
eozzy

Reputation: 68670

Field with value not exists (MySQL)

If I run this query:

SELECT
    wp_posts.ID,
    wp_posts.post_title,
    wp_posts.post_status,
    wp_postmeta.meta_key 
FROM
    wp_posts
    LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id 
WHERE
    post_type = 'product' AND 
    wp_postmeta.meta_key = 'cat_disabled' AND
    wp_postmeta.meta_value = 'on'

I get 5348 records, but doing the opposite (getting records where cat_disabled key and its value is NOT set, it takes forever:

SELECT
    wp_posts.ID,
    wp_posts.post_title,
    wp_posts.post_status,
    wp_postmeta.meta_key 
FROM
    wp_posts
    LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id 
WHERE
    post_type = 'product' AND 
    wp_postmeta.meta_key != 'cat_disabled' AND
    wp_postmeta.meta_value != 'on'

How do I optimize this query to get products where there's no meta_key with value cat_disabled set.

Upvotes: 1

Views: 58

Answers (2)

eozzy
eozzy

Reputation: 68670

SELECT
    ID 
FROM
    wp_posts 
WHERE
    ID NOT IN ( SELECT post_id FROM wp_postmeta WHERE meta_key = "cat_disabled" ) 
    AND post_type = "product"
    AND post_status = "publish"

Upvotes: 0

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

Without having all the schema and some data to test, this is a suggestion :

SELECT
    wp_posts.ID,
    wp_posts.post_title,
    wp_posts.post_status,
    wp_postmeta.meta_key 
FROM
    wp_posts
LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id 
WHERE
    post_type = 'product' AND 
    wp_postmeta.post_id NOT IN
    (SELECT post_id 
    FROM wp_postmeta 
    WHERE meta_key = 'cat_disabled' AND
        meta_value = 'on')

Upvotes: 1

Related Questions