title: What is causing this memory leak when (inner) joining this table?
body: <p>I have SQL that in my head, would and should run in under 1 second:</p>
  
  <pre><code>SELECT mem.`epid`,
         mem.`model_id`,
         em.`UKM_Make`,
         em.`UKM_Model`,
         em.`UKM_CCM`,
         em.`UKM_Submodel`,
         em.`Year`,
         em.`UKM_StreetName`,
         f.`fit_part_number`
  FROM `table_one` AS mem
  INNER JOIN `table_two` em ON mem.`epid` = em.`ePID`
  INNER JOIN `table_three` f ON `mem`.`model_id` = f.`fit_model_id`
  LIMIT 1;
  </code></pre>
  
  <p>When I run in the terminal this SQL executes in 16 seconds. However, if I remove the line:</p>
  
  <pre><code>INNER JOIN `table_three` f ON `mem`.`model_id` = f.`fit_model_id`
  </code></pre>
  
  <p>then it executes in 0.03 seconds. Unfortunately for me, I'm not to sure how to debug MYSQL performance issues. This causes my PHP script to run out of memory trying to execute the query.</p>
  
  <p>Here are my table structures:</p>
  
  <blockquote>
    <p>table_one</p>
  </blockquote>
  
  <pre><code>+----------+---------+------+-----+---------+-------+
  | Field    | Type    | Null | Key | Default | Extra |
  +----------+---------+------+-----+---------+-------+
  | epid     | int(11) | YES  |     | NULL    |       |
  | model_id | int(11) | YES  |     | NULL    |       |
  +----------+---------+------+-----+---------+-------+
  </code></pre>
  
  <blockquote>
    <p>table_two</p>
  </blockquote>
  
  <pre><code>+----------------+--------------+------+-----+---------+-------+
  | Field          | Type         | Null | Key | Default | Extra |
  +----------------+--------------+------+-----+---------+-------+
  | id             | int(11)      | NO   | PRI | NULL    |       |
  | ePID           | int(11)      | NO   |     | NULL    |       |
  | UKM_Make       | varchar(100) | NO   |     | NULL    |       |
  | UKM_Model      | varchar(100) | NO   |     | NULL    |       |
  | UKM_CCM        | int(11)      | NO   |     | NULL    |       |
  | UKM_Submodel   | varchar(100) | NO   |     | NULL    |       |
  | Year           | int(11)      | NO   |     | NULL    |       |
  | UKM_StreetName | varchar(100) | NO   |     | NULL    |       |
  | Vehicle Type   | varchar(100) | NO   |     | NULL    |       |
  +----------------+--------------+------+-----+---------+-------+
  </code></pre>
  
  <blockquote>
    <p>table_three</p>
  </blockquote>
  
  <pre><code>+-----------------+-------------+------+-----+---------+----------------+
  | Field           | Type        | Null | Key | Default | Extra          |
  +-----------------+-------------+------+-----+---------+----------------+
  | fit_fitment_id  | int(11)     | NO   | PRI | NULL    | auto_increment |
  | fit_part_number | varchar(50) | NO   |     | NULL    |                |
  | fit_model_id    | int(11)     | YES  |     | NULL    |                |
  | fit_year_start  | varchar(4)  | YES  |     | NULL    |                |
  | fit_year_end    | varchar(4)  | YES  |     | NULL    |                |
  +-----------------+-------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>The above is output from <code>describe $table_name</code></p>
  
  <p>Is there anything that I'm obviously missing and if not, how can I try to find out why including <code>table_three</code> causes such a slow response time?</p>
  
  <p><strong>EDIT ONE:</strong> </p>
  
  <p>After the indexing suggestion (used <code>CREATE INDEX fit_model ON table_three (fit_model_id)</code>, it performs the query in 0.00 seconds (in MYSQL). Removing the limit, is still running from after doing the suggestion ... so not quite there. Anton's suggestion about using EXPLAIN I used it and got this output:</p>
  
  <pre><code>+------+-------------+-------+------+---------------+-----------+---------+----------------------+-------+-------------------------------------------------+
  | id   | select_type | table | type | possible_keys | key       | key_len | ref                  | rows  | Extra                                           |
  +------+-------------+-------+------+---------------+-----------+---------+----------------------+-------+-------------------------------------------------+
  |    1 | SIMPLE      | mem   | ALL  | NULL          | NULL      | NULL    | NULL                 |  5587 | Using where                                     |
  |    1 | SIMPLE      | f     | ref  | fit_model     | fit_model | 5       | mastern.mem.model_id |    14 |                                                 |
  |    1 | SIMPLE      | em    | ALL  | NULL          | NULL      | NULL    | NULL                 | 36773 | Using where; Using join buffer (flat, BNL join) |
  +------+-------------+-------+------+---------------+-----------+---------+----------------------+-------+-------------------------------------------------+
  </code></pre>
  
  <p><strong>EDIT TWO</strong></p>
  
  <p>I've added a Foreign Key based on suggestions using the below query:</p>
  
  <pre><code>ALTER TABLE `table_one`
  ADD CONSTRAINT `model_id_fk_tbl_three`
  FOREIGN KEY (`model_id`)
  REFERENCES `table_three` (`fit_model_id`)
  </code></pre>
  
  <p>MYSQL is still executing the command - there are a lot of rows, so half-expecting this behaviour. With PHP I can break up the query and build my array like that, so I guess that possibly solves the issue - thought is there anything more I can do to try and reduce execution time?</p>