title: MySQL sorting on joined table column extremely slow (temp table)
body: <p>I have some tables:</p>
  
  <pre><code>object 
  person 
  project 
  [...] (some more tables) 
  type 
  </code></pre>
  
  <p>The object table has foreign keys to all other tables.</p>
  
  <p>Now I do a query like:</p>
  
  <pre><code>SELECT * FROM object 
  LEFT JOIN person ON (object.person_id = person.id) 
  LEFT JOIN project ON (object.project_id = project.id)
  LEFT JOIN [...] (all other joins)
  LEFT JOIN type ON (object.type_id = type.id)
  WHERE object.customer_id = XXX 
  ORDER BY object.type_id ASC
  LIMIT 25
  </code></pre>
  
  <p>This works perfectly well and fast, even for big resultsets. For example I have 90000 objects and the query takes about 3 seconds. The result ist quite big because the tables have a lot of columns and all of them are fetched. For info: I'm using Symfony with Propel, InnoDB and the "doSelectJoinAll"-function.</p>
  
  <p>But if do a query like (sort by type.name):</p>
  
  <pre><code>SELECT * FROM object 
  LEFT JOIN person ON (object.person_id = person.id) 
  LEFT JOIN project ON (object.project_id = project.id)
  LEFT JOIN [...] (all other joins)
  LEFT JOIN type ON (object.type_id = type.id)
  WHERE object.customer_id = XXX 
  ORDER BY type.name ASC
  LIMIT 25
  </code></pre>
  
  <p>The query takes about 200 seconds!</p>
  
  <p>EXPLAIN:</p>
  
  <pre><code>id  | select_type   | table     | type      | possible_keys | key       | key_len   | ref           | rows      | Extra
  1   | SIMPLE    | object    | ref       | object_FI_2   | object_FI_2   | 4     | const         | 164966    | Using where; Using temporary; Using filesort
  1   | SIMPLE    | person    | eq_ref    | PRIMARY   | PRIMARY   | 4         | db.object.person_id   | 1     
  1   | SIMPLE    | ...       | eq_ref    | PRIMARY   | PRIMARY   | 4         | db.object...._id  | 1     
  1   | SIMPLE    | type      | eq_ref    | PRIMARY   | PRIMARY   | 4         | db.object.type_id     | 1     
  </code></pre>
  
  <p>I saw in the processlist, that MySQL is creating a temporary table for such a sorting on a joined table.</p>
  
  <p>Adding an index to type.name didn't improve the performance. There are only about 800 type rows.</p>
  
  <p>I found out that the many joins and the big result is the problem, because if I do a query with just one join like:</p>
  
  <pre><code>SELECT * FROM object 
  LEFT JOIN type ON (object.type_id = type.id)
  WHERE object.customer_id = XXX 
  ORDER BY type.name ASC
  LIMIT 25
  </code></pre>
  
  <p>it works as fast as expected.</p>
  
  <p>Is there a way to improve such sorting queries on a big resultset with many joined tables? Or is it just a bad habit to sort on a joined table column and this shouldn't be done anyway?</p>
  
  <p>Thank you</p>