How to apply the MINUS efficiently on mysql query for tables with large data

Question

I have 2 tables as the following -

CREATE TABLE IF NOT EXISTS `nl_members` (
  `member_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `member_email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `member_confirmation_code` varchar(35) COLLATE utf8_unicode_ci NOT NULL,
  `member_enabled` enum('Yes','No') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Yes',
  PRIMARY KEY (`member_id`),
  UNIQUE KEY `TUC_nl_members_1` (`member_email`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=58520 ;

CREATE TABLE IF NOT EXISTS `nl_member_group_xref` (
  `group_id` int(10) unsigned NOT NULL,
  `member_id` int(10) unsigned NOT NULL,
  `member_subscribed` enum('Yes','No') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Yes',
  `subscribe_date` int(10) unsigned NOT NULL DEFAULT '0',
  `unsubscribe_date` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`group_id`,`member_id`),
  KEY `nl_members_nl_member_group_xref` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


ALTER TABLE `nl_member_group_xref`
 ADD CONSTRAINT `nl_members_nl_member_group_xref` FOREIGN KEY (`member_id`) REFERENCES `nl_members` (`member_id`),
 ADD CONSTRAINT `nl_member_groups_nl_member_group_xref` FOREIGN KEY (`group_id`) REFERENCES `nl_member_groups` (`group_id`);

Both has quite some large amount of data about millions of them.

What i want is to have an efficient was of applying the MINUS on result set.

For example,

i want to get all the users from Group1 with ID: 1 MINUS all users from Group2 with ID: 2 and Group3 with ID: 3

How can i do it efficiently? with the query running as fast as possible.

Update

What i want is like this -

in members table 'nl_members' i keep a list of all members, who could have been associated with one or more groups.

for each group association for a member there will be a row in the 'nl_member_group_xref' table.

so if a member is associated with 3 groups there will be 3 entries in the member_group_xref table.

Now what i want is to get all members included in group 1 but exclude members if they also belong to group 2 and group 3.

Hope this helps.

Answer 1

For your updated question you will need to join the two tables and group it with members_id: See below query if will display the result your looking for.

UPDATED:

  SELECT 
         nm.*, nmgx.*
    FROM nl_members nm
   INNER JOIN nl_member_group_xref nmgx
      ON nm.member_id = nmgx.member_id
    LEFT JOIN (SELECT 
                      nmgx2.member_id
                 FROM nl_member_group_xref nmgx2
                WHERE nmgx2.group_id <> 1) nmgx22
      ON nmgx22.member_id = nm.member_id
   WHERE nmgx22.member_id IS NULL
   GROUP BY nm.member_id;

Note: I used * to get all the field name. You get specific field so the query will be more faster as it only get less results. Ex. member_id like nm.member_id

If this is not what you looking for, just inform me then I'll update this query as accurate as I can