title: Joining tables on foreign key: MySQL performance
body: <p>I have two tables that have a foreign key constraint between them</p>
  
  <pre><code>Table event
  mysql&gt; describe event;
  +------------+------------------+------+-----+---------+-------+
  | Field      | Type             | Null | Key | Default | Extra |
  +------------+------------------+------+-----+---------+-------+
  | sid        | int(10) unsigned | NO   | PRI | NULL    |       |
  | cid        | int(10) unsigned | NO   | PRI | NULL    |       |
  | signature  | int(10) unsigned | NO   | MUL | NULL    |       |
  | timestamp  | datetime         | NO   | MUL | NULL    |       |
  | is_deleted | tinyint(1)       | NO   | MUL | 0       |       |
  +------------+------------------+------+-----+---------+-------+
  5 rows in set (0.00 sec)
  
  Table signature
  mysql&gt; describe signature;
  +--------------+------------------+------+-----+---------+----------------+
  | Field        | Type             | Null | Key | Default | Extra          |
  +--------------+------------------+------+-----+---------+----------------+
  | sig_id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
  | sig_name     | varchar(255)     | NO   | MUL | NULL    |                |
  | sig_class_id | int(10) unsigned | NO   | MUL | NULL    |                |
  | sig_priority | int(10) unsigned | YES  |     | NULL    |                |
  | sig_rev      | int(10) unsigned | YES  |     | NULL    |                |
  | sig_sid      | int(10) unsigned | YES  |     | NULL    |                |
  | sig_gid      | int(10) unsigned | YES  |     | NULL    |                |
  +--------------+------------------+------+-----+---------+----------------+
  7 rows in set (0.00 sec)
  </code></pre>
  
  <p>event.signature is a foreign key and links to signature.sig_id. Both have indexes as well</p>
  
  <p>Table event is large(say 1M records) while table signature will be comparatively small (Few thousand at most)</p>
  
  <p>Joined Queries that access any signature attribute take a very long time to execute. A look at explain</p>
  
  <pre><code>mysql&gt; explain select event.sid,event.cid,signature.sig_name from event join signature on signature.sig_id=event.signature;
  +----+-------------+-----------+------+--------------------------------+-----------------------+---------+-------------------------+------+--------------------------+
  | id | select_type | table     | type | possible_keys                  | key                   | key_len | ref                     | rows | Extra                    |
  +----+-------------+-----------+------+--------------------------------+-----------------------+---------+-------------------------+------+--------------------------+
  |  1 | SIMPLE      | signature | ALL  | PRIMARY,index_signature_sig_id | NULL                  | NULL    | NULL                    |  127 |                          |
  |  1 | SIMPLE      | event     | ref  | index_event_signature          | index_event_signature | 5       | snorby.signature.sig_id |   68 | Using where; Using index |
  +----+-------------+-----------+------+--------------------------------+-----------------------+---------+-------------------------+------+--------------------------+
  2 rows in set (0.00 sec)
  </code></pre>
  
  <p>While if no signature attribute is accessed</p>
  
  <pre><code>mysql&gt; explain select event.sid,event.cid from event join signature on signature.sig_id=event.signature;
  +----+-------------+-----------+-------+--------------------------------+------------------------+---------+-------------------------+------+--------------------------+
  | id | select_type | table     | type  | possible_keys                  | key                    | key_len | ref                     | rows | Extra                    |
  +----+-------------+-----------+-------+--------------------------------+------------------------+---------+-------------------------+------+--------------------------+
  |  1 | SIMPLE      | signature | index | PRIMARY,index_signature_sig_id | index_signature_sig_id | 4       | NULL                    |  127 | Using index              |
  |  1 | SIMPLE      | event     | ref   | index_event_signature          | index_event_signature  | 5       | snorby.signature.sig_id |   68 | Using where; Using index |
  +----+-------------+-----------+-------+--------------------------------+------------------------+---------+-------------------------+------+--------------------------+
  2 rows in set (0.00 sec)
  </code></pre>
  
  <p>As can be seen if Signature attribute is queried it does a full scan with ALL join type. </p>
  
  <p>Is it possible to rewrite the query to be faster? I ask this because this is a part of multiple table join and joining event with signature is the bottleneck that is slowing down the query tremendously</p>
  
  <p>I am using 5.1.52 MySQL and SQLAlchemy 0.7.8 as ORM</p>