Determine a set of id's that exist in a set of tables: options needed to speed up or parallelize the algorithm 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
This belongs on dba.stackexchange.com  but I don't have the rep to comment
(and the reason I don't have the points is I gave out a 100 point bounty)

You don't give the sql an algorithm.  You give it indexes and a good query (some times with query hints).  SQL will parallelize on its own. 

PartyID would be better as an int and then have another table for the text
Int or VarChar put and index on it  

select table1.partyID 
  from table1 
  join table2 
    on table2.partyID = table1.partyID 
  join table3 
    on table3.partyID = table1.partyID


for an or just do do a union  

select table1.partyID 
  from table1
union
select table2.partyID 
  from table2
...