title: Optimizing MySQL GROUP BY query
body: <p>I need to optimize this query:</p>
  
  <pre><code>SELECT 
      cp.id_currency_purchase,
      MIN(cr.buy_rate),
      MAX(cr.buy_rate)
  FROM currency_purchase cp
  JOIN currency_rate cr ON cr.id_currency = cp.id_currency AND cr.id_currency_source = cp.id_currency_source
  WHERE cr.rate_date &gt;= cp.purchase_date
  GROUP BY cp.id_currency_purchase
  </code></pre>
  
  <p>Now it takes about 0,5 s, which is way too long for me (it's used either updated very often, so query cache doesn't help in my case).</p>
  
  <p>Here's EXPLAIN EXTENDED output:</p>
  
  <pre><code>+------+-------------+-------+-------+--------------------------------+-------------+---------+-------------------------+-------+----------+-------------+
  | id   | select_type | table | type  | possible_keys                  | key         | key_len | ref                     | rows  | filtered | Extra       |
  +------+-------------+-------+-------+--------------------------------+-------------+---------+-------------------------+-------+----------+-------------+
  |    1 | SIMPLE      | cp    | index | id_currency_source,id_currency | PRIMARY     | 8       | NULL                    |     9 |   100.00 |             |
  |    1 | SIMPLE      | cr    | ref   | id_currency,id_currency_source | id_currency | 8       | lokatnik.cp.id_currency | 21433 |   100.00 | Using where |
  +------+-------------+-------+-------+--------------------------------+-------------+---------+-------------------------+-------+----------+-------------+
  </code></pre>
  
  <p>There are some great questions at stackoverflow for one table GROUP BY optimization (like: <a href="https://stackoverflow.com/a/11631480/1320081">https://stackoverflow.com/a/11631480/1320081</a>), but I really don't know how to optimize query based on JOIN-ed tables.</p>
  
  <p>How can I speed it up?</p>