Select rows with most recent date for the given date

Question

I have a table to manage the stock which looks like below

product    batch   qty  date
Grape      BAT0    42   10/3/2016
Apple      BAT1    12   11/3/2016
Banana     BAT2    15   11/3/2016
Orange     BAT3    24   11/3/2016
Pine       BAT4    28   11/3/2016
Apple      BAT1    9    12/3/2016
Banana     BAT2    10   12/3/2016
Pine       BAT4    25   12/3/2016
Apple      BAT1    5    13/3/2016
Banana     BAT2    6    13/03/2016
GreenApple BAT5    15   13/03/2016

If I pass the date 12/03/2016 then I need the last dated row to return as below

product batch   qty date
Grape   BAT0    42  10/3/2016
Orange  BAT3    24  11/3/2016
Apple   BAT1    9   12/3/2016
Banana  BAT2    10  12/3/2016
Pine    BAT4    25  12/3/2016

Also I have more than 160,000 rows in that table and it will grow each day.

How can I write an optimised query?

Answer 1

It's a bit hard to understand the logic of the query from the explanation, but I think you mean you get a date as input and you need the closest by date for each product?

If so, I have a solution, pretty sure not the optimal one, but maybe the logic can lead you in the right direction:

SELECT t.id, t.product
FROM table t
JOIN
(
    SELECT
        t.id,
        t.product, 
        t.date, MIN(ABS(UNIX_TIMESTAMP('yourdate') - UNIX_TIMESTAMP(t.date))) AS diff
    FROM table t
    GROUP BY t.product
) x ON x.diff = ABS(UNIX_TIMESTAMP('yourdate') - UNIX_TIMESTAMP(t.date)) 
AND x.product = t.product