title: Optimizing a mysql index on an ordered, range query
body: <p>I'm looking for help with some offensive mysql queries I currently have running against my server. My goal is to show the most expensive ebay items with end time's less than one month ago.</p>
  
  <p>I'm using MySQL 5.1.</p>
  
  <p>My query is as follows ('ebay_items' has ~350,000 rows):</p>
  
  <pre><code>explain SELECT `ebay_items`.* FROM `ebay_items` 
  WHERE (endtime &gt; NOW()-INTERVAL 1 MONTH) ORDER BY price desc\G;
  </code></pre>
  
  <p>yields:</p>
  
  <pre><code>*************************** 1. row ***************************
  id: 1
  select_type: SIMPLE
  table: ebay_items
  type: range
  possible_keys: endtime
  key: endtime
  key_len: 9
  ref: NULL
  rows: 71760
  Extra: Using where; Using filesort
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>This query results in an expensive 'filesort' using 71760 rows. </p>
  
  <pre><code>show indexes on ebay_items;
  </code></pre>
  
  <p>yields (I've only included the index in question, 'endtime'):</p>
  
  <pre><code>*************************** 7. row ***************************
  Table: ebay_items
  Non_unique: 1
  Key_name: endtime
  Seq_in_index: 1
  Column_name: endtime
  Collation: A
  Cardinality: 230697
  Sub_part: NULL
  Packed: NULL
  Null: YES
  Index_type: BTREE
  Comment: 
  *************************** 8. row ***************************
  Table: ebay_items
  Non_unique: 1
  Key_name: endtime
  Seq_in_index: 2
  Column_name: price
  Collation: A
  Cardinality: 230697
  Sub_part: NULL
  Packed: NULL
  Null: YES
  Index_type: BTREE
  Comment: 
  </code></pre>
  
  <p>Only the 'endtime' key of the composite endtime index (endtime, price) is being used. As far as I know, MySQL will not make use of a composite index effectively when dealing with a range query in conjunction with an 'order by' clause. </p>
  
  <p>Has anyone found a good workout for this issues? I'd primarily like to solve it at the DB level (either by a smarter use of indexes or schema changes) but I'm open to suggestions. </p>
  
  <p>One way that I could avoid the range query is to have a background task cycling through every X hours and marking an enum type field on ebay_items as '&lt; 1 day old', '&lt; 1 week old', '&lt; 1 month old', etc. I was hoping to solve the problem in a cleaner way.</p>
  
  <p>Is there any way to execute MySQL range query with an order by clause, queries in an efficient manner? </p>
  
  <p>Huge thanks for your help! </p>
  
  <p>Edit: Kohányi Róbert made the good point that I should clarify the exact problem I was having with the query. The query results in the disk I/O being pegged for it's duration. If several of these queries are running simultaneously, processes get backed up and the machine locks up. My assumption is that the filesort is eating the I/O. </p>
  
  <p>I should also mention that the table is using the MyISAM engine. Would it more performant and less I/O intensive to use the InnoDB engine? Thanks again.</p>