I am trying to determine a set of id's that that exist in a set of tables, but the size of the tables are > 10 million records, the database is denormalized, normalization is not an option and the query either takes to long to complete or never completes.
I'm not sure how to partition this sort of job so that it can be run in parallel so that it takes less time and so if a step/subsection fails it can continue from where it left off.
A simplified example would be: find the customer in 3 tables whose Id matches in all tables.
The initial idea was to start looking at how sql engine inner
LOOP JOIN actually works to try get ideas on how to partition it so that parts could be run in parallel. But without knowing anything about the various data science algorithms, I suspect there is some standard way to do this.
The rather cryptic original requirement was:
Compare party_id column in the three tables to identify the customer available in three table i.e. it is AND operation between three.
SAMPLE1.PARTY_ID AND SAMPLE2.PARTY_ID AND SAMPLE3.PARTY_ID
If the operation is OR then pick all the customers available in the three tables.
SAMPLE1.PARTY_ID OR SAMPLE2.PARTY_ID OR SAMPLE3.PARTY_ID
AND / OR are used between tables then performed the comparison as required.
SAMPLE1.PARTY_ID AND SAMPLE2.PARTY_ID OR SAMPLE3.PARTY_ID
I set up some 4 test tables each with with this definition
CREATE TABLE `TABLE1` ( `CREATED` datetime DEFAULT NULL, `PARTY_ID` varchar(45) NOT NULL, `GROUP_ID` varchar(45) NOT NULL, `SEQUENCE_ID` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`SEQUENCE_ID`) ) ENGINE=InnoDB AUTO_INCREMENT=978536 DEFAULT CHARSET=latin1;
Then added 1,000,000 records to each just random numbers in a range that should result in joins.
I used the following test query
SELECT `TABLE1`.`PARTY_ID` AS `pi1`, `TABLE2`.`PARTY_ID` AS `pi2`, `TABLE3`.`PARTY_ID` AS `pi3`, `TABLE4`.`PARTY_ID` AS `pi4` FROM `devt1`.`TABLE2` AS `TABLE2`, `devt1`.`TABLE1` AS `TABLE1`, `devt1`.`TABLE3` AS `TABLE3`, `devt1`.`TABLE4` AS `TABLE4` WHERE `TABLE2`.`PARTY_ID` = `TABLE1`.`PARTY_ID` AND `TABLE3`.`PARTY_ID` = `TABLE2`.`PARTY_ID` AND `TABLE4`.`PARTY_ID` = `TABLE3`.`PARTY_ID`
It's supposed to complete in under 10 min and for table sizes 10x larger. My test query still hasn't completed and it has been running for 15 min