title: MySQL Optimizing a subquery which uses a WHERE clause?
body: <p>In my query, I use a subquery which get the second last highest date of an offer for a given product.</p>
  
  <p>Here's my subquery:</p>
  
  <pre><code>LEFT JOIN (SELECT product_id, MAX(offer_date) AS sec_last_date 
             FROM t_offers AS s1  
             WHERE offer_date &lt; (SELECT MAX(offer_date)
                                 FROM t_offers 
                                 WHERE product_id=s1.product_id)
             GROUP BY product_id) AS t2 ON t2.product_id=p.id 
  LEFT JOIN t_offers AS o2 ON o2.product_id=t2.product_id AND 
                              o2.offer_date=t2.sec_last_date
  </code></pre>
  
  <p>It works fine, but for now there're only few rows in the t_offers table. </p>
  
  <p>It will probably not work that fine with thousands or millions of rows because of the <code>WHERE</code> clause which forces MySQL  to iterate the t_offers table for each <code>product_id</code>.</p>
  
  <p>How could I optimized this subquery ? </p>