title: MySQL: poor performance joining small to large table
body: <p>I have the following tables:</p>
  
  <pre><code>CREATE TABLE smalltable (
      smalltable_id VARCHAR(64) NOT NULL,
      bigtable_id VARCHAR(64),
      ...
      PRIMARY KEY (smalltable_id)
  ) ENGINE=InnoDB;
  
  CREATE TABLE bigtable (
      bigtable_id VARCHAR(64) NOT NULL,
      count BIGINT,
      PRIMARY KEY (bigtable_id)
  ) ENGINE=InnoDB;
  </code></pre>
  
  <p><code>smalltable</code> has about 8000 rows and <code>bigtable</code> about 40 million. I would like to retrieve those rows from <code>smalltable</code> where <code>smalltable.bigtable_id</code> is present in <code>bigtable</code>. The following query took almost 10 hours to complete:</p>
  
  <pre><code>SELECT * FROM smalltable
  INNER JOIN bigtable
  ON smalltable.bigtable_id = bigtable.bigtable_id;
  </code></pre>
  
  <p>Here is the output of <code>EXPLAIN</code>:</p>
  
  <pre><code>*************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: smalltable
     partitions: NULL
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 8610
       filtered: 100.00
          Extra: NULL
  *************************** 2. row ***************************
             id: 1
    select_type: SIMPLE
          table: bigtable
     partitions: NULL
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 38818260
       filtered: 100.00
          Extra: Using where; Using join buffer (Block Nested Loop)
  </code></pre>
  
  <p>I'm no expert in interpreting this, but it looks like MySQL is doing a sequential scan over both tables. If I write a little Python script that iterates over all rows in <code>smalltable</code> and executes a <code>SELECT</code> query on <code>bigtable</code> for each row in <code>smalltable</code> the whole thing finishes in 25 seconds. I'd like to have the same kind of performance with a single SQL query.</p>