How to Efficiently Fetch the Last Row for Each Group in MySQL Group By Queries?

Optimizing Last Row Fetching in MySQL Group By Queries

In MySQL, retrieving the last row for each group of a grouped data set requires an efficient approach. One common strategy is the correlated subquery method:

select * 
from foo as a
where a.id = (select max(id) from foo where uid = a.uid group by uid)
group by uid;

While this method works, it can be computationally expensive. A more efficient alternative is to use a join with a subquery, as demonstrated in the following query:

SELECT t1.* FROM foo t1
JOIN (SELECT uid, MAX(id) id FROM foo GROUP BY uid) t2
ON t1.id = t2.id AND t1.uid = t2.uid;

This approach optimizes performance by pre-calculating the maximum ID for each group in the subquery. By joining on this pre-calculated value, the query can efficiently retrieve the last row for each group.

Additionally, using EXPLAIN on both queries would provide valuable insights into the execution plan and resource consumption, allowing for further optimization. Another alternative is to use the LEFT JOIN method:

SELECT t1.* FROM foo t1
LEFT JOIN foo t2
ON t1.id < t2.id AND t1.uid = t2.uid
WHERE t2.id is NULL;

This query identifies the last row for each group by left joining on subsequent rows and filtering out those with non-null ID values. Ultimately, the choice of optimization approach depends on the specific data and performance requirements of the application.

The above is the detailed content of How to Efficiently Fetch the Last Row for Each Group in MySQL Group By Queries?. For more information, please follow other related articles on the PHP Chinese website!