title: Mysql Self-Join Performance
body: <p>Let's say I have a table looks like this:</p>
  
  <pre><code>+----+----------+------------+-------+------+
  | id | category |      state | A1code| val  |
  +----+----------+------------+-------+------+
  |  1 |        1 |    Florida | 13000 | 12   |
  |  2 |        1 |    Florida | 13001 | 14   |
  |  3 |        1 |    Florida | 13002 | 15   |
  |  4 |        2 |    Florida | 13000 | 12   |
  |  5 |        2 |    Florida | 13001 | 17   |
  |  6 |        2 |    Florida | 13002 | 16   |
  |  7 |        1 |  Calfornia | 13000 | 15   |
  |  8 |        1 |  Calfornia | 13001 | 13   |
  |  9 |        1 |  Calfornia | 13002 | 14   |
  | 10 |        2 |  Calfornia | 13000 | 12   |
  | 11 |        2 |  Calfornia | 13001 | 14   |
  | 12 |        2 |  Calfornia | 13002 | 16   |
  ....
  +----+----------+------------+------+
  </code></pre>
  
  <p>and I need to get the result in this fashion: </p>
  
  <pre><code>state, A1code, category1, category2
  Florida,13000, 12,12
  Florida,13001, 14,17
  Florida,13002, 15,16
  Calfornia,13000, 15,12
  Calfornia,13001, 13,14
  Calfornia,13002, 14,16
  ....
  </code></pre>
  
  <p>and im currently seeing the sql like this:</p>
  
  <pre><code>SELECT A.STATE, A.A1CODE, A.val AS category1, B.val AS category 2
  FROM DUMMY_TABLE A
  INNER JOIN DUMMY_TABLE B
  USING (STATE,A1CODE)
  WHERE A.category = 1 AND B.category = 2;
  </code></pre>
  
  <p>and with a table some 60k long, this query takes ~40secs to run on the computer. </p>
  
  <p>now with a query like </p>
  
  <pre><code>SELECT A.STATE, A.A1CODE, A.val AS category1
  FROM DUMMY_TABLE A
  WHERE A.category = 1
  </code></pre>
  
  <p>running at less than 0.1sec, and what I want is just combining results from two category, there must be a faster way to do this? </p>
  
  <p>(this problem comes when i try to port a db from MS ACCESS to MYSQL. the same query that took ~40s on MYSQL takes ~1sec to run in MS ACCESS.)</p>
  
  <p>thanks in advance</p>