title: Fastest way of doing a 1 to 1 left join on tables with a 1 to many relationship (MySQL)
body: <p>I have two tables that have a 1 to many relationship, which I'm doing a 1:1 left join on. The query returns the correct results but it shows up in my slow query log (it takes up to 5s). Is there a better way to write this query?</p>
  
  <pre><code>select * from
  tablea a left join tableb b 
    on a.tablea_id = b.tablea_id
    and b.tableb_id = (select max(tableb_id) from tableb b2 where b2.tablea_id = a.tablea_id)
  </code></pre>
  
  <p>i.e. I would like <code>TableA</code> left joined to the row in <code>TableB</code> with the largest <code>tableb_id</code>.</p>
  
  <pre><code>TableA
  tablea_id
  1
  2
  
  TableB
  tableb_id, tablea_id, data
  1, 1, x
  2, 1, y
  
  Expected Result
  tablea_id, tableb_id, data
  1, 2, y
  2, null, null
  </code></pre>
  
  <p>TableA has an index on <code>tablea_id</code> and TableB has a composite index on <code>tablea_id,tableb_id</code>.</p>
  
  <p>Explain Output</p>
  
  <pre><code>+----+--------------------+---------------+--------+-----------------+---------------+---------+----------------------+-------+-------------+
  | id | select_type        | table         | type   | possible_keys   | key           | key_len | ref                  | rows  | Extra       |
  +----+--------------------+---------------+--------+-----------------+---------------+---------+----------------------+-------+-------------+
  |  1 | PRIMARY            | c             | index  | NULL            | department_id | 4       | NULL                 | 18966 | Using index |
  |  1 | PRIMARY            | recent_cv_lut | eq_ref | PRIMARY,case_id | PRIMARY       | 4       | func                 |     1 |             |
  |  2 | DEPENDENT SUBQUERY | cases_visits  | ref    | case_id         | case_id       | 4       | abcd_records_v2.c.id |     2 | Using index |
  +----+--------------------+---------------+--------+-----------------+---------------+---------+----------------------+-------+-------------+
  </code></pre>