MYSQL: SELECT users from one user group and exclude users from another group - optimisation

I have an optimisation question here.

Background

I have a 12000 users in a user table, on record per user. Each user can be in zero or more groups. I have a groups table with 45 groups and a groups_link table with 75000 records (to facilitate the many to many relationship).

I am making a querying screen which allows a user to filter users from the user list.

Aim

Specifically, I need help with: Selecting all users that are in one group bu开发者_如何学编程t are not in another group.

DB Structure

Query

My current query which runs too slowly...

 SELECT U.user_id,U.user_email
FROM (sc_module_users AS U)
JOIN sc_module_users_groups_links AS group_join ON group_join.user_id = U.user_id
LEFT JOIN sc_module_users_groups_links AS excluded_group_join ON group_join.user_id = U.user_id
WHERE group_join.group_id IN (27) AND excluded_group_join.group_id NOT IN (19) OR excluded_group_join.group_id IS NULL AND U.user_subscribed=1 AND U.user_active=1
GROUP BY U.user_id,U.user_id

This query takes 9 minutes to complete, it returns 11,000 records (out of 12,000).

Explain

Here's the explain on that query:

Can anyone help me optimise this to below the 1 minute mark...?

After 3 revisions, I changed it to this

    SELECT U.user_id,U.user_email FROM (sc_module_users AS U) WHERE ( user_country LIKE '%australia%' ) AND 
EXISTS (SELECT group_id FROM sc_module_users_groups_links l WHERE group_id in (31) AND l.user_id=U.user_id) AND 
NOT EXISTS (SELECT group_id FROM sc_module_users_groups_links l WHERE group_id in (27) AND l.user_id=U.user_id) 
AND U.user_subscribed=1 AND U.user_active=1 GROUP BY U.user_id

' mucccch faster

EDIT: removed my query suggestion but the index stuff should still apply:

The indexes on the sc_module_users_groups_links could be improved by creating a composite index just on user_id and group_id. The order of the columns in the index can have an impact - i believe having user_id first should perform better.

You could also try removing the link_id and just using a composite primary key since the link_id doesn't seem to serve any other purpose.

I believe the very first thing you need to do is to place parentheses:

// should be  
.. AND ( excluded_group_join.group_id NOT IN (19) 
  OR excluded_group_join.group_id IS NULL)  AND ....