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.

Do you have existing query? If you say you want to minus all user from group2 and 3 to 1 I'm thinking that it is number result. But you say all user then it means the data for each user. So my understanding about your question is LIMIT = 0,(G1 - (G2 + G3)) where limit will include at the end of your query. If my understanding is wrong, would you mind explain it more? — ace, Apr 11 '11 at 05:35

score 0 · Answer 1 · answered Apr 11 '11 at 04:57

0

Have you tried using the MINUS operator?

answered Apr 11 '11 at 04:57

Daniel Protopopov

6,778
3
23
39

With very large data set, the MINUS will be double time consuming, as it would have to fetch both result sets and then eliminating the duplicates – anjan Apr 11 '11 at 05:08
The OP is asking about minusing sets not numbers. MySQL doesn't have an `EXCEPT` / `MINUS` operator in that sense. – Martin Smith Apr 11 '11 at 10:14

ace · Accepted Answer · 2011-04-13T05:52:12.650

0

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

edited Apr 13 '11 at 05:52

answered Apr 11 '11 at 08:19

ace

6,775
7
38
47

I have check the code and it's working, but still you need to check it base on your result it may differ to what you really want. For now just try it. – ace Apr 11 '11 at 08:44
Thanks for the reply. but this is not what i m looking for. it simple inner joins, but need to find members of group1 MINUS all members from group2 and group3 – anjan Apr 13 '11 at 04:50
@anjan, I update my post, have a look. I hope this is what you looking for, with regards to optimization I'm not sure if this is faster than with your current working query. – ace Apr 13 '11 at 05:36
thnx :) i will give it a try and let u know – anjan Apr 13 '11 at 08:59

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

2 Answers2