title: Why MySQL only use composite index but not separate indexes?
body: <p>I have following table</p>
  
  <pre><code>CREATE TABLE `test` (
    `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `a` int(11) NOT NULL,
    `b` int(11) NOT NULL
  );
  </code></pre>
  
  <p>And I need to do following query</p>
  
  <pre><code>SELECT * FROM `test` ORDER BY a, b LIMIT 1;
  </code></pre>
  
  <p>If I add a composite index</p>
  
  <pre><code>ALTER TABLE `t_test` ADD INDEX a_b(`a`, `b`);
  </code></pre>
  
  <p>It works</p>
  
  <pre><code>&gt; EXPLAIN SELECT * FROM `test` ORDER BY a, b LIMIT 1;
  +------+-------------+-------+-------+---------------+------+---------+------+------+-------+
  | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra |
  +------+-------------+-------+-------+---------------+------+---------+------+------+-------+
  |    1 | SIMPLE      | test  | index | NULL          | a_b  | 8       | NULL |    1 |       |
  +------+-------------+-------+-------+---------------+------+---------+------+------+-------+
  </code></pre>
  
  <p>But if I add two index separately</p>
  
  <pre><code>ALTER TABLE `t_test` ADD INDEX a(`a`), ADD INDEX b(`b`);
  </code></pre>
  
  <p>It fails</p>
  
  <pre><code>&gt; EXPLAIN SELECT * FROM `test` ORDER BY a, b LIMIT 1;
  +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
  | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
  +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
  |    1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using filesort |
  +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
  </code></pre>
  
  <p>Even I add <code>FORCE INDEX</code>, it doesn't work.</p>
  
  <p>As my personal understand, it should use both <code>index a</code> and <code>index b</code> and just a little smaller than composite index.</p>
  
  <p>Even if I'm wrong, it should at least use <code>index a</code> first and then use filesort to sort b.</p>
  
  <p>Does this sort operator really can't use separated indexes? If yes, please explain why it doesn't work. If no, do you have any solution to let it work with separate indexes? Thanks in advance.</p>
  
  <h2>EDIT</h2>
  
  <p>For example I have 100 lines. I can use <code>index a</code> first to sort them. And then in each group with same <code>a</code> value, I can use <code>index b</code> to sort them.</p>
  
  <p>Why this way can't work on MySQL?</p>