How to Efficiently Retrieve the Last Record in Each Group in MySQL?

MySQL: Extracting the Final Entry from Each Data Group

A frequent database operation involves retrieving the most recent record within each data group. While GROUP BY is often used, it can lead to performance issues.

Suboptimal Approach

The following query, using a subquery and descending order, attempts this:

<code class="language-sql">SELECT
  *
FROM (SELECT
  *
FROM messages
ORDER BY id DESC) AS x
GROUP BY name</code>

This method is inefficient because the outer query scans the entire table for each group, regardless of more efficient alternatives.

Optimized Solution (MySQL 8.0 )

MySQL 8.0 and later versions offer window functions for improved group-wise operations. ROW_NUMBER() efficiently identifies the last record in each group:

<code class="language-sql">WITH ranked_messages AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
  FROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;</code>

This query ranks each row within its group and then selects only the top-ranked (last) record from each.

Alternative (Pre-MySQL 8.0)

For older MySQL versions (before 8.0), this approach is suitable:

<code class="language-sql">SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
 ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;</code>

This uses a LEFT JOIN to find rows without a higher id within the same group, effectively isolating the last record in each.

Performance Considerations

The optimal query depends heavily on your data size, group distribution, and existing indexes. Benchmarking your specific scenario is crucial for selecting the most efficient solution.

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