title: index help for a MySQL query using greater-than operator and ORDER BY
body: <p>I have a table with at least a couple million rows and a schema of all integers that looks roughly like this:</p>
  
  <pre><code>start
  stop
  first_user_id
  second_user_id
  </code></pre>
  
  <p>The rows get pulled using the following queries:</p>
  
  <pre><code>SELECT * 
    FROM tbl_name 
   WHERE stop &gt;= M 
     AND first_user_id=N  
     AND second_user_id=N 
  ORDER BY start ASC
  
  SELECT * 
    FROM tbl_name 
   WHERE stop &gt;= M 
     AND first_user_id=N 
  ORDER BY start ASC
  </code></pre>
  
  <p>I cannot figure out the best indexes to speed up these queries. The problem seems to be the ORDER BY because when I take that out the queries are fast.</p>
  
  <p>I've tried all different types of indexes using the standard index format:</p>
  
  <pre><code>ALTER TABLE tbl_name ADD INDEX index_name (index_col_1,index_col_2,...)
  </code></pre>
  
  <p>And none of them seem to speed up the queries. Does anyone have any idea what index would work? Also, should I be trying a different type of index? I can't guarantee the uniqueness of each row so I've avoided UNIQUE indexes.</p>
  
  <p>Any guidance/help would be appreciated. Thanks!</p>
  
  <p>Update: here are a list of the indexes, I didn't include this originally since I've taken a shotgun approach and added a ton of indexes looking for one that works:</p>
  
  <pre><code>start_index: [start, first_user_id, second_user_id]
  stop_index: [stop, first_user_id, second_user_id]
  F1_index: [first_user_id]
  F2_index: [second_user_id]
  F3_index: [another_id]
  test_1_index: [first_user_id,stop,start]
  test_2_index: [first_user_id,start,stop]
  test_3_index: [start,stop,first_user_id,second_user_id]
  test_4_index: [stop,first_user_id,second_user_id,start]
  test_5_index: [stop,start]
  </code></pre>
  
  <p>And here is the EXPLAIN output.</p>
  
  <pre><code>*************************** 1. row ***************************
  id: 1
  select_type: SIMPLE
  table: listing
  type: index_merge
  possible_keys: stop_index,F1_index,F3_index,test_1_index,test_2_index,test_4_index,test_5_index
  key: F1_index,F3_index
  key_len: 5,5
  ref: NULL
  rows: 238
  Extra: Using intersect(F1_index,F3_index); Using where; Using filesort
  </code></pre>
  
  <p><strong>Update for posterity</strong></p>
  
  <p>We ended up completely re-evaluating how we were querying the table and chose these indexes:</p>
  
  <pre><code>index_select_1: [first_user_id,start,stop]
  index_select_2: [first_user_id,second_user_id,start,stop]
  </code></pre>
  
  <p>and then we select on the table with queries like these:</p>
  
  <pre><code>SELECT * 
    FROM tbl_name 
   WHERE first_user_id=N
     AND start &gt;= M 
  ORDER BY start ASC
  
  SELECT * 
    FROM tbl_name 
   WHERE first_user_id=N   
     AND second_user_id=N
     AND start &gt;= M 
  ORDER BY start ASC
  </code></pre>
  
  <p>Thanks to everyone that answered, you really helped me think through the problem.</p>