How to efficiently SQL select newest entries from a MySQL database?

Question

I have a database containing stock price history. I want to select most recent prices for every stock that is listed. I know PostreSQL has a DISTINCT ON statement that would suit ideally here.

Table columns are name, closingPrice and date; name and date together form a unique index.

The easiest (and very uneffective) way is

SELECT * FROM stockPrices s
WHERE s.date =
(SELECT MAX(date) FROM stockPrices si WHERE si.name = s.name);

Much better approach I found is

SELECT * FROM stockPrices s JOIN
(SELECT name, MAX(date) AS date
FROM stockPrices si GROUP BY name) lastEntry
ON s.name = lastEntry.name AND s.date = lastEntry.date;

What would be an efficient way to do this? What indexes should I create?

duplicate of:
http://stackoverflow.com/questions/49404/sql-query-to-get-latest-price

Answer 1

See similar post

Answer 2

I think that your second approach is very efficient. What's its problem?

You have to add indexes to name and date.