title: MySQL Optimising Join
body: <p>I have inherited the following query:</p>
  
  <pre><code>    select [...]
      from
          t_bike_use
              join t_bike
                   on t_bike_use.bid = t_bike.id
              join t_user
                   on t_bike_use.uid = t_user.id
              left join t_violation
                        on t_violation.useid = t_bike_use.id
              left join t_trade
                        on t_bike_use.id = t_trade.record_id
              left join t_admin
                        on t_bike_use.admin_id = t_admin.id
              join t_user_detail
                   on t_user.id = t_user_detail.uid
      group by t_bike_use.id
      order by t_violation.id desc
  </code></pre>
  
  <p>I'm having trouble getting this join to execute quickly. Currently it takes <code>1.6s</code>, and since it is executed frequently it tends bring the database to its knees.</p>
  
  <p>The <code>t_bike_use</code>, <code>t_bike</code>, <code>t_user</code> and <code>t_trade</code> tables all have between 2k and 8k rows. However, there are foreign keys between <code>t_bike_use</code> and <code>t_bike</code> and <code>t_user</code> but not one for <code>t_trade.record_id</code>.</p>
  
  <p>If I remove the join with <code>t_trade</code> execution time falls to 400ms.</p>
  
  <pre><code>    left join t_trade
                on t_bike_use.id = t_trade.record_id
  </code></pre>
  
  <p>I have an index on <code>t_trade.record_id</code> but MySQL 5.6 does not want to use it.</p>
  
  <p>How can this query be optimised?</p>
  
  <p>Here's the output of <code>EXPLAIN</code>.</p>
  
  <pre><code>1  SIMPLE  t_bike_use     ALL     PRIMARY,uuid,fk_uid,fk_bid  &lt;&gt;       &lt;&gt;  &lt;&gt;                   1771  Using temporary; Using filesort
  1  SIMPLE  t_violation    ALL     &lt;&gt;                          &lt;&gt;       &lt;&gt;  &lt;&gt;                   1     Using where; Using join buffer (Block Nested Loop)
  1  SIMPLE  t_user         eq_ref  PRIMARY                     PRIMARY  4   t_bike_use.uid       1   
  1  SIMPLE  t_bike         eq_ref  PRIMARY                     PRIMARY  4   t_bike_use.bid       1   
  1  SIMPLE  t_user_detail  ref     fk_uid                      fk_uid   4   t_bike_use.uid       1   
  1  SIMPLE  t_trade        ALL     record_id                   &lt;&gt;       &lt;&gt;  &lt;&gt;                   2085  Range checked for each record (index map: 0x2)
  1  SIMPLE  t_admin        eq_ref  PRIMARY                     PRIMARY  4   t_bike_use.admin_id  1   
  </code></pre>