join - MySql: joining same table with millions rows

Question

I have a table with millions of rows (SF_COLLECTIONS)

ID MEMBERID COLLECTIONID CARDID STATE (D / M)
1  1        1            1      D
2  1        1            2      D
3  2        1            1      M
4  2        1            2      M
5  2        1            3      D
6  1        1            3      M

and I have to cross those that have MEMBERID = 1 and STATE = D with those that have MEMBERID = 2 and STATE = M and vice versa

This is my query

SELECT 1
    FROM sf_collections AS rac
    INNER JOIN sf_collections AS myrac 
        ON 
        (myrac.cardid = rac.cardid AND 
            (
                (myrac.state = "M" AND rac.state = "D") OR 
                (myrac.state = "D" AND rac.state = "M")
            )
        ) 
    WHERE
    rac.memberid = 1 AND myrac.memberid = 2
    GROUP BY rac.memberid

(response time about 4 seconds)

Is this a valid approach or there is a better way to improve performance?

Sample data set:

CREATE TABLE `sf_collections` (
 `id` int(11) NOT NULL auto_increment,
 `memberid` int(11) NOT NULL,
 `collectionid` int(11) NOT NULL,
 `cardid` int(11) NOT NULL,
 `state` varchar(1) NOT NULL,
 PRIMARY KEY  (`id`),
 UNIQUE KEY `sf_collections_pkey` (`memberid`,`collectionid`,`cardid`,`state`),
 KEY `collectionid` (`collectionid`),
 KEY `memberid` (`memberid`),
 KEY `cardid` (`cardid`),
 KEY `state` (`state`)
) ENGINE=MyISAM AUTO_INCREMENT=22627806 DEFAULT CHARSET=latin1

INSERT INTO sf_collections (memberid,collectionid,cardid,state) VALUES
(1,1,1,'D'),
(1,1,2,'D'),
(1,1,3,'M'),
(2,1,1,'M'),
(2,1,2,'M'),
(2,1,3,'D');

SELECT 1
    FROM sf_collections AS rac
    INNER JOIN sf_collections AS myrac 
        ON 
        (myrac.cardid = rac.cardid AND 
            (
                (myrac.state = "M" AND rac.state = "D") OR 
                (myrac.state = "D" AND rac.state = "M")
            )
        ) 
    WHERE
    rac.memberid = 1 AND myrac.memberid = 2
    GROUP BY rac.memberid

and db-fiddle

Thanks

EDIT: MySql is 5.0 (very old and cannot upgrade this)

question from:https://stackoverflow.com/questions/65897454/mysql-joining-same-table-with-millions-rows

Answer 1

Replace the INDEX(cardid) with INDEX(cardid, state)

If practical, don't check for both D->M and M->D; just do one direction. This will cut in half the effort.

Avoid the SELECT ... GROUP BY ... by switching to EXISTS ( SELECT 1 ... ). If there are multiple matching rows, this will speed things up. If you will eventually be "listing the matches", we may as well see what you want rather than nitpicking over the bad use of GROUP BY. Will you be using a GROUP_CONCAT?

Move from MyISAM to InnoDB. Even in 5.0 it may have been intrinsically faster for this query.

DROP INDEX(memberid) since the unique index handles such.

Do you need id for anything? If not, get rid of it and promote the 4-column UNIQUE index to be the PRIMARY KEY.

If state is just a flag (0/1), the INDEX(state) will probably never be used; drop it.