How to Delete All but the Latest N Records in a MySQL Table?

Efficiently Removing Older MySQL Records, Keeping Only the Latest N

Deleting all but the newest N entries in a MySQL table requires careful query construction. A straightforward approach using LIMIT within a DELETE statement is often unsuccessful due to MySQL's limitations.

A common flawed attempt looks like this:

<code class="language-sql">DELETE FROM `table`
ORDER BY `id` ASC
LIMIT ((SELECT COUNT(*) FROM `table`) - N);</code>

This fails because MySQL doesn't allow subqueries directly within the LIMIT clause.

A robust solution employs a nested subquery:

<code class="language-sql">DELETE FROM `table`
WHERE `id` NOT IN (
  SELECT `id`
  FROM (
    SELECT `id`
    FROM `table`
    ORDER BY `id` DESC
    LIMIT 42 -- Replace 42 with your desired N
  ) AS `subquery`
);</code>

This method first selects the IDs of the N most recent records (ordered by id descending) using an inner subquery. The outer query then deletes all records whose IDs are not present in this inner selection. This effectively retains only the latest N entries.

Remember to replace 42 with your desired value of N. For optimal performance with large datasets, further optimization techniques might be necessary. Explore alternative approaches and consider indexing your id column for significant speed improvements. Consult additional resources for advanced solutions tailored to specific performance needs.

The above is the detailed content of How to Delete All but the Latest N Records in a MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!