title: Partition a very large INNER JOIN SQL query
body: <p>The sql query is fairly standard inner join type.
  For example comparing n tables to see which customerId's exist in all n tables would be a basic WHERE ... AND type query.</p>
  
  <p>The problem is the size of the tables are > 10 million records. The database is denormalized. Normalization is not an option.
  The query either takes to long to complete or never completes.</p>
  
  <p>I'm not sure if it's relevant but we are using spring xd job modules for other types of queries.</p>
  
  <p>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.</p>
  
  <p>Other posts with similar problem suggest using alternative methods besides the database engine like implementing a LOOP JOIN in code or using MapReduce or Hadoop, having never used either I'm unsure if they are worth looking into for this use case.</p>
  
  <p>What is the standard approach to this sort of operation, I'd expect it to be fairly common. I might be using the wrong search terms to research approaches because I haven't come across any stock standard solutions or clear directions.</p>
  
  <p>The rather cryptic original requirement was:</p>
  
  <p>Compare party_id column in the three very large tables to identify the customer available in three table 
  i.e if it is AND operation between three. 
  SAMPLE1.PARTY_ID AND SAMPLE2.PARTY_ID AND SAMPLE3.PARTY_ID</p>
  
  <p>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</p>
  
  <p>AND / OR are used between tables then performed the comparison as required. SAMPLE1.PARTY_ID AND SAMPLE2.PARTY_ID OR SAMPLE3.PARTY_ID</p>
  
  <p>I set up some 4 test tables each with with this definition</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Then added 1,000,000 records to each just random numbers in a range that should result in joins.</p>
  
  <p>I used the following test query</p>
  
  <pre><code>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`
  </code></pre>
  
  <p>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 </p>