Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Have you tried SELECT id, name, price, start_date, item_id versus using * ? –  Ryan Dec 20 '13 at 13:40
    
Yes, duration is the same, because I need all columns but I will try LEFT JOIN after group by. Duration goes up when I select name column. –  Xdg Dec 20 '13 at 13:44
add comment

1 Answer 1

Duration goes to 1 sec when I'm selecting name column. But when I use this query, time is only 0.094 sec:

SELECT h.* FROM (SELECT * FROM (SELECT id, item_id FROM price_list x WHERE start_date <= '2013-12-15 12:14:18' ORDER BY start_date DESC) a GROUP BY item_id) b LEFT JOIN price_list h ON h.id = b.id

Thank you, Ryan!

share|improve this answer
    
You're welcome. –  Ryan Dec 20 '13 at 13:53
    
-1 for still using select * just type in the columns. –  Zane Jun 19 at 20:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.