Q:

MySQL Optimizing a subquery which uses a WHERE clause? 

In my query, I use a subquery which get the second last highest date of an offer for a given product. 
Here's my subquery:
LEFT JOIN (SELECT product_id, MAX(offer_date) AS sec_last_date 
           FROM t_offers AS s1  
           WHERE offer_date < (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

It works fine, but for now there're only few rows in the t_offers table.  
It will probably not work that fine with thousands or millions of rows because of the WHERE clause which forces MySQL  to iterate the t_offers table for each product_id. 
How could I optimized this subquery ?  

A:

Sub queries are often not great in MySQL due to not using indexes for the joins. 
However it might be worth trying a sub query with a join, rather than a sub query with a sub query:-
LEFT JOIN (SELECT s1.product_id, MAX(s1.offer_date) AS sec_last_date 
           FROM t_offers AS s1  
           INNER JOIN t_offers AS s2
           ON s2.product_id = s1.product_id
           AND s2.offer_date > s1.offer_date
           GROUP BY s1.product_id) AS t2 ON t2.product_id=p.id 

