Optimize mysql NOT IN query

Question

I have two tables users, and postmeta. I want to take users id's from users table and want to filter users id's which are not present in postmeta table.

Here is my mysql query

SELECT u.ID from users WHERE u.ID NOT IN (SELECT DISTINCT(meta_value) from postmeta WHERE meta_key = '_customer_user')

Above query is working fine but as i have thousands of users and also postmeta table is large it is taking too much time.

Can anyone help me on how to optimize this query?

you should read this post first, all you need is at theese post [large table optimization](https://stackoverflow.com/questions/5557838/mysql-optimization-of-huge-table) — fahmi hammadi, Jan 09 '18 at 09:59
Try this: `SELECT DISTINCT(u.id) from users AS u, postmeta AS p WHERE p.meta_key = '_customer_user' AND u.id <> p.meta_value ` — yrv16, Jan 09 '18 at 11:04
How much RAM do you have? What is the value of `innodb_buffer_pool_size`? Are the tables `ENGINE=InnoDB`? — Rick James, Jan 13 '18 at 15:09

score 0 · Answer 1 · answered Jan 09 '18 at 09:49

0

Look at the expalin,and use not exists replaced

answered Jan 09 '18 at 09:49

sunzheng04

29
2

Raunak Gupta · Answer 2 · 2018-01-10T07:04:34.773

0

Try this way

SELECT u.ID
FROM diet_users AS u
WHERE NOT EXISTS
    (SELECT pm.meta_value
     FROM diet_postmeta AS pm
     WHERE meta_key = '_customer_user'
       AND pm.meta_value = u.ID)

Hope this helps!

edited Jan 10 '18 at 07:04

answered Jan 09 '18 at 09:58

Raunak Gupta

10,412
3
58
97

Remove `DISTINCT`, it is totally useless. – Rick James Jan 10 '18 at 01:07

score 0 · Accepted Answer · answered Jan 10 '18 at 01:13

0

SELECT  u.ID
    FROM  users
    WHERE NOT EXISTS ( SELECT *
                FROM postmeta
                WHERE meta_key = '_customer_user'
                  AND meta_value = u.ID )

Since this smells like WP, I recommend some tips on wp_postmeta performance here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta (I don't think the tips will significantly help this query.)

answered Jan 10 '18 at 01:13

Rick James

135,179
13
127
222

Unfortunately query is still taking a lot of time as i have around 2 lakh (2 hundred thousands) users in my user table. – Mubashir Jan 13 '18 at 13:47
How many rows in `postmeta` have `meta_key = '_customer_user'`? – Rick James Jan 13 '18 at 15:07
Assuming `meta_value` is `LONGTEXT`, there is no good way to avoid scanning all 4M rows for `_customer_user`. To put it bluntly, the link between users and customers _must_ be done with a column, _not_ a "postmeta" key-value mechanism. If you can't achieve that in WP, then I recommend you abandon WP. – Rick James Jan 14 '18 at 15:02
Can you please tell me how i can run mysql count function on the following query to get the total number of rows returned. SELECT u.ID FROM wp_users AS u LEFT OUTER JOIN wp_comments AS c ON u.ID = c.user_id LEFT OUTER JOIN wp_posts AS p ON u.ID = p.post_author LEFT OUTER JOIN wp_links AS l ON u.ID = l.link_owner WHERE c.user_id IS NULL AND p.post_author IS NULL AND l.link_owner IS NULL AND YEAR(u.user_registered) = 2017 AND u.ID NOT IN (SELECT DISTINCT(meta_value) FROM wp_postmeta WHERE `meta_key` = '_customer_user' ) GROUP BY u.ID – Mubashir Jan 14 '18 at 15:27
@mubashir - `SELECT COUNT(*) FROM ( SELECT ... ) AS x;` – Rick James Jan 14 '18 at 17:58

Optimize mysql NOT IN query

3 Answers3