title: How to avoid FileSort when using LEFT JOIN + ORDER BY?
body: <p>mysql-5.6.24-win32.1432006610</p>
  
  <p>I have two simple tables, <code>TUser(id, name)</code> and <code>TMessage(id, uid, message)</code>. <code>TUser</code> holds the users, and <code>TMessage</code> holds messages of users.</p>
  
  <p>The SQL is as below, also see: <a href="http://sqlfiddle.com/#!9/7f099" rel="nofollow">http://sqlfiddle.com/#!9/7f099</a></p>
  
  <pre><code>CREATE TABLE TUser(
      id     INT UNSIGNED     PRIMARY KEY    NOT NULL    AUTO_INCREMENT,
      name   VARCHAR(128) NOT NULL
  );
  
  
  CREATE TABLE TMessage(
      id      INT UNSIGNED    PRIMARY KEY    NOT NULL    AUTO_INCREMENT,
      uid     INT UNSIGNED    NOT NULL,
      message VARCHAR(256)    NOT NULL
  );
  
  
  CREATE INDEX TMessageIndexUid ON TMessage(uid);
  </code></pre>
  
  <p>Insert some data:</p>
  
  <pre><code>INSERT INTO TUser (name) VALUES 
       ('jack')
      ,('rose')
      ,('peter');
  
  
  INSERT INTO TMessage(uid, message) VALUES
       (1, 'Hello jack')
      , (1, 'Jack, how are you')
      , (1, 'Good morning jack')
      , (2, 'I love you, rose')
      , (3, 'Peter, please call back')
      , (3, 'What are you doing, Peter');
  </code></pre>
  
  <p>When I run the following LEFT JOIN + ORDER BY query, FileSort shows in the EXPLAIN result:</p>
  
  <pre><code>EXPLAIN
  SELECT *
    FROM        TUser
    LEFT JOIN   TMessage 
    ON          TUser.id=TMessage.uid
    WHERE       TUser.id=3
    ORDER BY    TMessage.id DESC;
  
  
  
  id  select_type table    type  possible_keys    key              key_len ref   rows Extra
  1   SIMPLE      TUser    const PRIMARY;         PRIMARY          4       const 1    Using temporary; Using filesort
  1   SIMPLE      TMessage ref   TMessageIndexUid TMessageIndexUid 4       const 2 \N
  </code></pre>
  
  <p>Is there something wrong?</p>