Get Last Record in Each MySQL Group, the efficient way and all possible alternatives

This article discusses all possible alternatives for getting the first or last records from each mysql group.
Consider this sample table ( user_log ) for example:

In this table, there are 8 records of 2 users [user_id = 1, 2].
We want a query to get either first or last record in each user_id group. i.e. ( first or last records with user_id = 1, 2 ).

Here are possible solutions, their “Explain” statements and performance matrix (every query executed 3 times to check timing) to check which query would be best according to your requirements.

Query 1

Query 1 shows the conceptually simplest approach which is querying the table with id where id is in sub query which returns the maximum id group by user_id. Explain statement shows the query is executed in two parts as PRIMARY and DEPENDENT SUBQUERY iterating whole table 2 times.
As we can see in the performance matrix below the Query 1 is time consuming and hence not recommend.

Performance Matrix Chart

Query 2

Query 2 shows the approach where we query the table with the help of derived table t_max. Explain statement shows the query is executed in two parts as PRIMARY and DERIVED iterating whole table 2 times.
As we can see in the performance matrix below the Query 2 is better than Query 1 but still time consuming and hence not recommended.

Performance Matrix Chart

Query 3

Query 3 shows the approach of Joins. Explain statement shows the query is executed in two parts as SIMPLE and SIMPLE iterating whole table 2 times.
As we can see in the performance matrix below the Query 3 too time consuming so not recommended.

Performance Matrix Chart

Query 4

Query 4 shows the approach of Joins with derived table. Explain statement shows the query is executed in three parts as PRIMARY, PRIMARY and DERIVED but the table is not iterated 2 times here.
As we can see in the performance matrix below the Query 4 is very time efficient so it is recommended.

Performance Matrix Chart

Query 5

Query 5 shows the approach of Simple query and then string processing on columns to get the result we want. Explain statement shows the query is executed in only one part as SIMPLE so the table is iterated only once.
As we can see in the performance matrix below the Query 5 is also time efficient compared with Query 1, Query 2 and Query 3 so it is recommended.

Performance Matrix Chart

As we can see from the data matrix Query 4 and Query 5 are best suited to handle this problem.

The performance of this queries can very from data sets to data sets, here the data set taken is symmetrical (each user wise 4 rows), if data is asymmetrical these result will slightly differ, but it is beyond doubt that Query 4 and Query 5 will outperform every other query.

Common mistake in using MAX:

A simple mistake Developers make in this type of queries is using MAX function to get the last records from each group. Let’s see by example.

consider these 2 queries
SELECT max(id),user_id,first_name,last_name FROM user_log group by user_id DESC
SELECT max(id),id,user_id,first_name,last_name FROM user_log group by user_id DESC

Most developers just use query 1 thinking, MAX(id) will give them the row with id = MAX(id) which is wrong.

You can catch the problem if you run the 2nd query with id in column. You can clearly see the MAX(id) is 8 and id is returning 5, so the result is wrong. And unlike in our example if you do not have different data in columns other than id [first_name, last_name], it is difficult to catch this problem.

So, let me clear one misconception that MAX(id), is a function that returns the maximum of the rows in that column, Using MAX(id) does not return maximum id of rows in the query result.

Structural changes:

Let’s discuss what structural changes we could have done to improve the performance.

So basically we have an option of adding a column that returns the latest record, for each group last row(or first depend on your query) added a flag last= 1, and make all other rows’ flag last= 0.
1. By Trigger
2. By Manual programming changes

Which ever works for you, but if you can afford to add an column then it would largely simplify your query if not you can always use Query 4 or Query 5.