title: Left Join on Subquery with criteria in MySQL alternative?
body: <pre><code>SELECT a.tag,CONCAT(u.first_name,' ',u.last_name)
  FROM assets a   
  LEFT JOIN (SELECT asset_id,assigned_to_id
         FROM asset_activity
         WHERE assigned IN (SELECT MAX(assigned)
                            FROM asset_activity
                            GROUP BY asset_id)) 
         v ON v.asset_id = a.id
  LEFT JOIN users u ON v.assigned_to_id = u.id  
  WHERE ($1 IS NULL OR u.last_name LIKE $1)     
  </code></pre>
  
  <p>Since MySQL performs horribly with left joins on a subquery, I need to find some other method to do this. I can select what I need with subqueries within the select, but it needs to be conditional. It should only return the records that match the LIKE, and with subqueries, it would still return a record from assets with a null value for assigned_to, so I can't do that. </p>
  
  <p>EXECUTION PLAN:</p>
  
  <pre><code>id select_type        table          type     possible_keys  key     key_len  ref  rows              Extra    
  1  PRIMARY            a              ALL      null           null     null    null             1,447
  1  PRIMARY            &lt;derived2&gt;     ALL      null           null     null    null             1,396
  1  PRIMARY            u              eq_ref   PRIMARY        PRIMARY  4       v.assigned_to_id 1 
  2  DERIVED            asset_activity ALL      null           null     null    null             1,400 Using where 
  3  DEPENDENT SUBQUERY asset_activity index    null           asset_id 4       null             1,400 Using filesort
  </code></pre>
  
  <p>INDEXES:</p>
  
  <pre><code>Table   Non_unique        Key_name  Seq_in_index      Column_name       Collation Cardinality Sub_part Packed   Null  Index_type    Comment
  assets  0                 PRIMARY   1                 id                A         144         ""                       BTREE       ""    
  assets  1                 serial    1                 serial            A         1447                          YES    BTREE       ""
  assets  1                 serial    2                 cal_num           A         1447                          YES    BTREE       ""
  
  Table          Non_unique Key_name          Seq_in_index Column_name       Collation Cardinality Sub_part Packed Null Index_type Comment
  asset_activity 0          PRIMARY           1            id                A         1400        ""                   BTREE      ""
  asset_activity 1          asset_id          1            asset_id          A                     ""                   BTREE      ""
  asset_activity 1          location_id       1            location_id       A                                     YES  BTREE      ""
  asset_activity 1          assigned_to_id    1            assigned_to_id    A                                     YES  BTREE      ""
  asset_activity 1          assigned_to_table 1            assigned_to_table A                                     YES  BTREE      ""
  asset_activity 1          created           1            created           A                     ""                   BTREE      ""
  </code></pre>