title: Optimizing MySQL query with inner join
body: <p>I've spent a lot of time optimizing this query but it's starting to slow down with larger tables. I imagine these are probably the worst types of questions but I'm looking for some guidance. I'm not really at liberty to disclose the database schema so hopefully this is enough information. Thanks,</p>
  
  <pre><code>SELECT tblA.id, tblB.id, tblC.id, tblD.id
  FROM tblA, tblB, tblC, tblD
  INNER JOIN (SELECT max(tblB.id) AS xid
                  FROM tblB
                  WHERE tblB.rdd = 11305
                  GROUP BY tblB.index_id
                  ORDER BY NULL) AS rddx
             ON tblB.id = rddx.xid
  WHERE
      tblA.id = tblB.index_id
      AND tblC.name = tblD.s_type
      AND tblD.name = tblA.s_name
  GROUP BY tblA.s_name
  ORDER BY NULL;
  </code></pre>
  
  <p>There is a one-to-many relationship between:</p>
  
  <ul>
  <li>tblA.id and tblB.index_id</li>
  <li>tblC.name and tblD.s_type</li>
  <li>tblD.name and tblA.s_name</li>
  </ul>
  
  <pre>
  +----+-------------+------------+--------+---------------+-----------+---------+------------------------------+-------+------------------------------+
  | id | select_type | table      | type   | possible_keys | key       | key_len | ref                          | rows  | Extra                        |
  +----+-------------+------------+--------+---------------+-----------+---------+------------------------------+-------+------------------------------+
  |  1 | PRIMARY     | derived2   | ALL    | NULL          | NULL      | NULL    | NULL                         | 32568 | Using temporary              |
  |  1 | PRIMARY     | tblB       | eq_ref | PRIMARY       | PRIMARY   | 8       | rddx.xid                     |     1 |                              |
  |  1 | PRIMARY     | tblA       | eq_ref | PRIMARY       | PRIMARY   | 8       | tblB.index_id                |     1 | Using where                  |
  |  1 | PRIMARY     | tblD       | eq_ref | PRIMARY       | PRIMARY   | 22      | tblA.s_name                  |     1 | Using where                  |
  |  1 | PRIMARY     | tblC       | eq_ref | PRIMARY       | PRIMARY   | 22      | tblD.s_type                  |     1 |                              |
  |  2 | DERIVED     | tblB       | ref    | rdd_idx       | rdd_idx   | 7       |                              | 65722 | Using where; Using temporary |
  +----+-------------+------------+--------+---------------+-----------+---------+------------------------------+-------+------------------------------+
  </pre>