I have a price_list table where I store historical prices for each item.
columns are - id, name, price, start_date, item_id
When a price is changed, a one new row is inserted with a current start_date. (Start_date is a datetime format).
Now I need to select prices of all items for an exact datetime. Problem is that this query is slow (1.058 sec):
SELECT * FROM (SELECT * FROM price_list h WHERE start_date <= '2013-12-15 12:14:18' ORDER BY start_date DESC) a GROUP BY item_id
Very slow is a GROUP BY. Without it it takes 0.075 sec, but each item (item_id) is there a multiple times.
bad: SELECT * FROM price_list h WHERE start_date <= '2013-12-15 12:14:18' ORDER BY item_id, start_date DESC
Please, can this query be rewritten to be quicker?