How to efficiently select new SQL records from a MySQL database?

Question

Possible duplicate:
SQL query to get the latest price

I have a database containing stock price history. I want to select the latest prices for each stock that is listed. I know that PostreSQL has DISTINCT ON , which is perfect here.

Columns of the table name , closingPrice and date ; name and date together form a unique index.

The easiest (and very inefficient) way is

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

The significantly better approach I have 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 effective way to do this? What indexes should I create?

duplicate:
SQL query to get the latest price

Answer 1

See similar post

Answer 2

I think your second approach is very effective. What is the problem?

You need to add indexes for the name and date.