title: JOINs being done in weird order; messing up ORDER BY?
body: <p>Let's say I have three tables - users, servers and payments. Each user can have multiple servers and each server can have multiple payments. Let's also say I wanted to find the most recent payments and get info about the servers / customers those payments are attached to. Here's a query that could do this:</p>
  
  <pre><code>SELECT *
  FROM payments p
  JOIN customers c ON p.custID = c.custID
  JOIN servers s ON s.serverID = p.serverID
  WHERE c.hold = 0
      AND c.archive = 0
  ORDER BY p.paymentID DESC
  LIMIT 10;
  </code></pre>
  
  <p>The problem is that when I run EXPLAIN on this query I get this:</p>
  
  <pre><code>id   select_type   table   type   possible_keys            key                key_len   ref                 rows     Extra
  1    SIMPLE        c       ref    PRIMARY,hold_archive     hold_archive       3         const,const         28728    Using where; Using index; Using temporary; Using filesort
  1    SIMPLE        p       ref    custID                   custID             5         customers.custID    3        Using where
  1    SIMPLE        s       eq_ref PRIMARY                  PRIMARY            4         payments.serverID   1        Using index
  </code></pre>
  
  <p>The problem is that the query takes a while to run. If I remove the ORDER BY it becomes 10x as fast. But I need the ORDER BY. Here's the EXPLAIN when I remove the ORDER BY:</p>
  
  <pre><code>id   select_type   table   type   possible_keys            key                key_len   ref                 rows     Extra
  1    SIMPLE        c       ref    PRIMARY,hold_archive     hold_archive       3         const,const         28728    Using where; Using index
  1    SIMPLE        p       ref    custID                   custID             5         customers.custID    3        Using where
  1    SIMPLE        s       eq_ref PRIMARY                  PRIMARY            4         payments.serverID   1        Using index
  </code></pre>
  
  <p>So the big difference here is that "Using temporary" and "Using filesort" are missing from the Extra column.</p>
  
  <p>It seems like the reason, in this case, is that the column I'm doing the ORDER BY on isn't the first column in the EXPLAIN.</p>
  
  <p>Another observation. If I remove one of the WHERE clauses (whilst keeping the ORDER BY) it speeds up similarily, but I need both WHERE's. Here's an example EXPLAIN of that:</p>
  
  <pre><code>id   select_type   table   type   possible_keys            key                key_len   ref                 rows     Extra
  1    SIMPLE        p       index  custID,serverID          PRIMARY            4         NULL                10       Using where
  1    SIMPLE        c       eq_ref PRIMARY,hold_archive     PRIMARY            4         payments.custID     1        Using where
  1    SIMPLE        s       eq_ref PRIMARY                  PRIMARY            4         payments.serverID   1        Using index
  </code></pre>
  
  <p>Here the ORDER BY column /is/ being done on the first column of the EXPLAIN. But why is MySQL re-arranging the order the tables are JOINed in and how can I make it so it doesn't do that? You can force indexes in MySQL but it doesn't seem like that'd help..</p>
  
  <p>Any ideas?</p>