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

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

From stackoverflow skolima

• See similar post

  skolima : I fail at search.
  From Galwegian

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

  You have to add indexes to name and date.

  Mike Woodhouse : Well, you have to add an index if performance requires it. If it's ten stocks and a year's daily data, I wouldn't be concerned: MySQL is relatively good at table scanning.

  Vinko Vrsalovic : If he had few data he wouldn't be asking for an efficient way to do this, no? Even the first, obvious approach would be enough.
  From Vinko Vrsalovic