Improving speed of SQL query with MAX, WHERE, and GROUP BY on three different columns

Question

I am attempting to speed up a query that takes around 60 seconds to complete on a table of ~20 million rows. For this example, the table has three columns (id, dateAdded, name). id is the primary key. The indexes I have added to the table are: ``` (dateAdded) (name) (id, name) (id, name, dateAdded) ``` The query I am trying to run is: ``` SELECT MAX(id) as id, name FROM exampletable WHERE dateAdded <= '2014-01-20 12:00:00' GROUP BY name ORDER BY NULL; ``` The date is variable from query to query. The objective of this is to get the most recent entry for each name at or before the date added. When I use explain on the query it tells me that it is using the (id, name, dateAdded) index. ``` +----+-------------+------------------+-------+------------------+----------------------------------------------+---------+------+----------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-----…

Accepted Answer

There is a great Stack Overflow post on optimization of Selecting rows with the max value in a column: https://stackoverflow.com/a/7745635/633063

This seems a little messy but works very well:

```
SELECT example1.name, MAX(example1.id)
FROM exampletable example1
INNER JOIN (
select name, max(dateAdded) dateAdded
from exampletable
where dateAdded  <= '2014-01-20 12:00:00' 
group by name
) maxDateByElement on example1.name = maxDateByElement.name AND example1.dateAdded = maxDateByElement.dateAdded
GROUP BY name;
```

Improving speed of SQL query with MAX, WHERE, and GROUP BY on three different columns

Solution

Problem

Related problems