Mysql Question posted in
The official MySQL documentation can be found here.

Mysql find users whose type is only admin

I wrote this query to get the list of all the ids in user table, but when i run this I get Lost connection to MySQL server during query, as the no of rows in user table is more than 5 million

SELECT id FROM users.user 
where user_type_id=(select id from users.user_type where user_type = 'admin') 
and email_add not in  
(select email_add from users.user 
where user_type_id = (select id from users.user_type where user_type = 'normaluser'));
Tags:

2

Answers


  1. 0

    Prefer joins rather than IN (subquery)

    SELECT u.id
FROM user AS u
JOIN user_type AS ut1 ON u.user_type_id = ut1.id
LEFT JOIN (
    SELECT email_add
    FROM user AS u
    JOIN user_type AS ut ON u.user_type_id = ut.id
    WHERE ut.user_type = 'normaluser'
) AS ut2 ON u.email_add = ut2.email_add
WHERE ut1.user_type = 'admin' AND ut2.email_add IS NULL

    Make sure there’s an index on user.user_type_id and user.email_add.

    Login or Signup to reply.
  2. 0

    Changing your not in to not exists should be much better, assuming email_add is indexed:

    SELECT id
FROM users.user u1
WHERE user_type_id = (SELECT id FROM users.user_type WHERE user_type = 'admin') 
AND NOT EXISTS (
    SELECT 1
    FROM users.user u2
    WHERE u2.user_type_id = (SELECT id FROM users.user_type WHERE user_type = 'normaluser')
    AND u2.email_add = u1.email_add
);

    If it’s still super slow, please update your question with the DDL for your user table and the execution plan (EXPLAIN SELECT ...).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search