How can i do better query in mysql

Question

I want to search a user that is not in specific group. I have a working query in MYSQL and i need some advice and some help. Here is my query.

SELECT id, fullname FROM users WHERE
id NOT IN (
                SELECT user_id FROM group_members WHERE group_id = $groupId
            )            
AND fullname LIKE $serchKey

the subquery is getting all id on a group then use it as reference in WHERE clause (id NOT IN) of main query, I have no large data by now. i think if i have large data and getting all id as reference use in (id NOT IN) i think it will took large time before getting the result. what can you say? can you give me a good suggestions for this. thanks

Answer 1

What you have done is the best way as I can think of with relate to the condition and code maintainability.

SELECT id, fullname 
FROM users 
WHERE id NOT IN (SELECT user_id FROM group_members WHERE group_id = $groupId)
  AND fullname LIKE $serchKey

But there is an alternative using LEFT JOIN which is arguable on the performance. (I didnt check this SQL, please check it in your DB)

SELECT distinct u.id
FROM users u
LEFT JOIN group_members gm
  ON u.id = gm.user_id
WHERE gm.user_id IS NULL 
  AND u.fullname like $serchkey