title: Big SQL SELECT performance difference when using <= against using < on a DATETIME column
body: <p>Given the following table:</p>
  
  <pre><code>desc exchange_rates;
  +------------------+----------------+------+-----+---------+----------------+
  | id               | int(11)        | NO   | PRI | NULL    | auto_increment | 
  | time             | datetime       | NO   | MUL | NULL    |                | 
  | base_currency    | varchar(3)     | NO   | MUL | NULL    |                | 
  | counter_currency | varchar(3)     | NO   | MUL | NULL    |                | 
  | rate             | decimal(32,16) | NO   |     | NULL    |                | 
  +------------------+----------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>I have added indexes on <code>time</code>, <code>base_currency</code> and <code>counter_currency</code>, as well as a composite index on (time, base_currency, counter_currency), but I'm seeing a big performance difference when I perform a <code>SELECT</code> using <code>&lt;=</code> against using <code>&lt;</code>.</p>
  
  <p>The first <code>SELECT</code> is:</p>
  
  <pre><code>ExchangeRate Load (95.5ms)   
  SELECT * FROM `exchange_rates` WHERE (time &lt;= '2009-12-30 14:42:02' and base_currency = 'GBP' and counter_currency = 'USD') LIMIT 1
  </code></pre>
  
  <p>As you can see this is taking 95ms.</p>
  
  <p>If I change the query such that I compare time using <code>&lt;</code> rather than <code>&lt;=</code> I see this:</p>
  
  <pre><code>ExchangeRate Load (0.8ms)   
  SELECT * FROM `exchange_rates` WHERE (time &lt; '2009-12-30 14:42:02' and base_currency = 'GBP' and counter_currency = 'USD') LIMIT 1
  </code></pre>
  
  <p>Now it takes less than 1 millisecond, which sounds right to me.  Is there a rational explanation for this behaviour?</p>
  
  <p>The output from <code>EXPLAIN</code> provides further details, but I'm not 100% sure how to intepret this:</p>
  
  <pre><code>-- Output from the first, slow, select
  SIMPLE      | 5,5     | exchange_rates | 1  | index_exchange_rates_on_time,index_exchange_rates_on_base_currency,index_exchange_rates_on_counter_currency,time_and_currency | index_merge | Using intersect(index_exchange_rates_on_counter_currency,index_exchange_rates_on_base_currency); Using where | 813  |     | index_exchange_rates_on_counter_currency,index_exchange_rates_on_base_currency
  
  -- Output from the second, fast, select
  SIMPLE      | 5       | exchange_rates | 1  | index_exchange_rates_on_time,index_exchange_rates_on_base_currency,index_exchange_rates_on_counter_currency,time_and_currency | ref  | Using where | 4988 | const | index_exchange_rates_on_counter_currency
  </code></pre>
  
  <p>(Note: I'm producing these queries through <code>ActiveRecord</code> (in a Rails app) but these are ultimately the queries which are being executed)</p>