Fast MAX, GROUP BY on the concatenation of mulliple columns

I have a table with 4 columns: name, date, version,and value. There's an composite index on all four, in that order. It has 20M rows: 2.000 names, approx 1.000 dates per name, approx 10 versions per date.

I'm trying to get a list that give for all names the highest date, the highest version on that date, and the associated value.

When I do

  SELECT name,
         MAX(date) 
    FROM table 
GROUP BY name

I get good performance and the database uses the composite index

However, when I join the table to this in order to get the MAX(version) per name the开发者_StackOverflow社区 query takes ages. There must be a way to get the result in about the same magnitude of time as the SELECT statement above? I can easily be done by using the index.

Try this: (I know it needs a few syntax tweaks for MySQL... ask for them and I will find them)

INSERT INTO #TempTable
SELECT name, MAX(Date) as Date
FROM table
Group By name

select table.name, table.date, max(table.version) as version
from table
inner join #TempTable on table.name = #temptable.name and table.date = #temptable.date
group by table.name, table.date