title: How to make order by faster?
body: <p>I have a table with several columns to order by. Those columns are <strong>float values with their corresponding indexes</strong> on them. The table has <strong>around 5 million rows</strong>.</p>
  
  <p>The table structure id something like that:</p>
  
  <pre><code>MyTable(id,a,b,c,d,e,f,g,h)
  MyWords(id,word)
  TableWords(wordID,MyTableID)
  </code></pre>
  
  <p>I need to run this query in a faster way which is basically a 100 top. I would like to have some suggestions on how to do that better:</p>
  
  <pre><code>select sql_no_cache a.* 
  from MyWords w
  left join TableWords wl on w.id = wl.wordID
  left join MyTable a on wl.MyTableID = a.id
  where w.word = 'WordToSearch'
  order by a.a desc
  limit 100
  </code></pre>
  
  <p><strong>The indexes:</strong></p>
  
  <pre><code>ALTER TABLE `a` ADD INDEX `a` (`a`);
  ALTER TABLE `a` ADD INDEX `b` (`b`);
  </code></pre>
  
  <p><strong>Using explain:</strong></p>
  
  <pre><code>1   SIMPLE  w   const   uniqueWord,fulltext_word    uniqueWord  63  const   1   Using temporary; Using filesort
  1   SIMPLE  wl  ref wordID  wordID  4   const   5597    
  1   SIMPLE  a   eq_ref  PRIMARY,id  id  4   test.wl.a   1   
  </code></pre>
  
  <p><strong>Describe TableWords:</strong></p>
  
  <pre><code>MyTableID   int(11)   NO    MUL
  wordID      int(11)   NO    MUL
  </code></pre>
  
  <p><strong>Describe MyWords:</strong></p>
  
  <pre><code>id      int(11)     NO  PRI     auto_increment
  word    varchar(20) YES UNI     
  </code></pre>
  
  <p><strong>Describe MyTable:</strong></p>
  
  <pre><code>id  int(11) NO  PRI auto_increment
  a   float   YES MUL 
  b   float   YES MUL 
  </code></pre>
  
  <p>I can order by my table and it is fast, but in the moment I make a left join to that table it doesnt use the indexes anymore. I put more data in my question.</p>
  
  <p>Thanks</p>