Consulta SQL: ¿Eliminar todos los registros de la tabla excepto la última N?

Sé que estoy resucitando una pregunta bastante vieja, pero recientemente me encontré con este tema, pero necesitaba algo que escala a grandes números bien. No había ningún dato de rendimiento existente, y ya que esta pregunta ha tenido un poco de atención, pensé en publicar lo que encontré.

Las soluciones que realmente funcionaron fueron la sub-consulta doble de Alex Barrett/NOT IN método (similar a de Bill Karwin ), y de Quassnoi LEFT JOIN método.

Desafortunadamente, ambos métodos crean tablas temporales intermedias muy grandes y el rendimiento se degrada rápidamente a medida que el número de registros no que se eliminan se vuelve grande.

Lo que decidí utiliza la doble sub-consulta de Alex Barrett (¡gracias!) pero usa <= en lugar de NOT IN:

DELETE FROM `test_sandbox`
  WHERE id <= (
    SELECT id
    FROM (
      SELECT id
      FROM `test_sandbox`
      ORDER BY id DESC
      LIMIT 1 OFFSET 42 -- keep this many records
    ) foo
  )

Utiliza OFFSET para obtener el id del N th registro y elimina ese registro y todos los registros anteriores.

Dado que el pedido ya es un asunción de este problema (ORDER BY id DESC), <= es un ajuste perfecto.

Es mucho más rápido, ya que la tabla temporal generada por la subconsulta contiene solo un registro en lugar de N registros.

Caso de prueba

Probé los tres métodos de trabajo y el nuevo método anterior en dos casos de prueba.

Ambos casos de prueba usan 10000 filas existentes, mientras que la primera prueba mantiene 9000 (elimina las 1000 más antiguas) y la segunda prueba mantiene 50 (elimina las más antiguas 9950).

+-----------+------------------------+----------------------+
|           | 10000 TOTAL, KEEP 9000 | 10000 TOTAL, KEEP 50 |
+-----------+------------------------+----------------------+
| NOT IN    |         3.2542 seconds |       0.1629 seconds |
| NOT IN v2 |         4.5863 seconds |       0.1650 seconds |
| <=,OFFSET |         0.0204 seconds |       0.1076 seconds |
+-----------+------------------------+----------------------+

Lo interesante es que el método <= ve un mejor rendimiento en todos los ámbitos, pero en realidad mejora cuanto más se mantiene, en lugar de peor.

Desafortunadamente para todas las respuestas dadas por otras personas, no puede DELETE y SELECT desde una tabla dada en la misma consulta.

DELETE FROM mytable WHERE id NOT IN (SELECT MAX(id) FROM mytable);

ERROR 1093 (HY000): You can't specify target table 'mytable' for update 
in FROM clause

Tampoco puede MySQL soportar LIMIT en una subconsulta. Estas son limitaciones de MySQL.

DELETE FROM mytable WHERE id NOT IN 
  (SELECT id FROM mytable ORDER BY id DESC LIMIT 1);

ERROR 1235 (42000): This version of MySQL doesn't yet support 
'LIMIT & IN/ALL/ANY/SOME subquery'

La mejor respuesta que se me ocurre es hacer esto en dos etapas:

SELECT id FROM mytable ORDER BY id DESC LIMIT n; 

Recoge los id y conviértelos en una cadena separada por comas:

DELETE FROM mytable WHERE id NOT IN ( ...comma-separated string... );

(Normalmente interpolar una lista separada por comas en una instrucción SQL introduce algún riesgo de SQL inyección, pero en este caso los valores no provienen de una fuente no confiable, se sabe que son valores id de la propia base de datos.)

Nota: Aunque esto no hace el trabajo en una consulta única, a veces una solución más simple, get-it-done es la más efectiva.

DELETE  i1.*
FROM    items i1
LEFT JOIN
        (
        SELECT  id
        FROM    items ii
        ORDER BY
                id DESC
        LIMIT 20
        ) i2
ON      i1.id = i2.id
WHERE   i2.id IS NULL

Si su id es incremental, use algo como

delete from table where id < (select max(id) from table)-N

Para eliminar todos los registros excepto te last N puede usar la consulta reportada más tarde.

Es una consulta única pero con muchas sentencias, por lo que en realidad no es una consulta única de la manera en que se pretendía en la pregunta original.

También necesita una variable y una instrucción incorporada (en la consulta) preparada debido a un error en MySQL.

Espero que pueda ser útil de todos modos...

nnn son las filas de a mantener y theTable es el mesa en la que estás trabajando.

Asumo que tienes un registro autoincrementing llamado id

SELECT @ROWS_TO_DELETE := COUNT(*) - nnn FROM `theTable`;
SELECT @ROWS_TO_DELETE := IF(@ROWS_TO_DELETE<0,0,@ROWS_TO_DELETE);
PREPARE STMT FROM "DELETE FROM `theTable` ORDER BY `id` ASC LIMIT ?";
EXECUTE STMT USING @ROWS_TO_DELETE;

Lo bueno de este enfoque es rendimiento: He probado la consulta en una base de datos local con aproximadamente 13.000 registros, manteniendo los últimos 1.000. Se ejecuta en 0,08 segundos.

El script de la respuesta aceptada...

DELETE FROM `table`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `table`
    ORDER BY id DESC
    LIMIT 42 -- keep this many records
  ) foo
);

Tarda 0,55 segundos. Unas 7 veces más.

Estoy ejecutando MySQL 5.5.25 en un MacBookPro i7 con SSD

DELETE FROM table WHERE ID NOT IN
(SELECT MAX(ID) ID FROM table)

Prueba la siguiente consulta:

DELETE FROM tablename WHERE id < (SELECT * FROM (SELECT (MAX(id)-10) FROM tablename ) AS a)

La sub consulta interna devolverá el valor top 10 y la consulta externa borrará todos los registros excepto los top 10.

ELIMINAR DE LA tabla DONDE id NO ESTÁ (SELECCIONAR id DE LA tabla ORDEN POR id, desc LÍMITE 0, 10)

Esto también debería funcionar:

DELETE FROM [table] INNER JOIN (SELECT [id] FROM (SELECT [id] FROM [table] ORDER BY [id] DESC LIMIT N) AS Temp) AS Temp2 ON [table].[id] = [Temp2].[id]

¿Qué pasa con :

SELECT * FROM table del 
         LEFT JOIN table keep
         ON del.id < keep.id
         GROUP BY del.* HAVING count(*) > N;

Devuelve filas con más de N filas antes. Podría ser útil ?

Usar id para esta tarea no es una opción en muchos casos. Por ejemplo - tabla con estados de twitter. Aquí hay una variante con el campo de marca de tiempo especificado.

delete from table 
where access_time >= 
(
    select access_time from  
    (
        select access_time from table 
            order by access_time limit 150000,1
    ) foo    
)

Solo quería agregar esto a la mezcla para cualquiera que use Microsoft SQL Server en lugar de MySQL. La palabra clave 'Limit' no es compatible con MSSQL, por lo que necesitará usar una alternativa. Este código funcionó en SQL 2008, y se basa en este post SO. https://stackoverflow.com/a/1104447/993856

-- Keep the last 10 most recent passwords for this user.
DECLARE @UserID int; SET @UserID = 1004
DECLARE @ThresholdID int -- Position of 10th password.
SELECT  @ThresholdID = UserPasswordHistoryID FROM
        (
            SELECT ROW_NUMBER()
            OVER (ORDER BY UserPasswordHistoryID DESC) AS RowNum, UserPasswordHistoryID
            FROM UserPasswordHistory
            WHERE UserID = @UserID
        ) sub
WHERE   (RowNum = 10) -- Keep this many records.

DELETE  UserPasswordHistory
WHERE   (UserID = @UserID)
        AND (UserPasswordHistoryID < @ThresholdID)

Es cierto que esto no es elegante. Si puede optimizar esto para Microsoft SQL, comparta su solución. ¡Gracias!

Si también necesita eliminar los registros basados en alguna otra columna, entonces aquí hay una solución:

DELETE
FROM articles
WHERE id IN
    (SELECT id
     FROM
       (SELECT id
        FROM articles
        WHERE user_id = :userId
        ORDER BY created_at DESC LIMIT 500, 10000000) abc)
  AND user_id = :userId

¿Por qué no

DELETE FROM table ORDER BY id DESC LIMIT 1, 123456789

Simplemente borre todo excepto la primera fila (el orden es DESC!), usando un numerador muy muy grande como segundo argumento LÍMITE. Ver aquí

Responder esto después de mucho tiempo...Me encontré con la misma situación y en lugar de usar las respuestas mencionadas, vine con la siguiente -

DELETE FROM table_name order by ID limit 10

Esto eliminará los registros 1st 10 y mantendrá los últimos registros.