Delete Query with Limit in Mysql

If you need to perform some clean up in your database one important think you come across, is how many live records in your database are optimal. So let say for our example we like to have 500 records in our live table at a time. However you cannot choose a date field as a deciding fact. Imagine if you deleted every thing in the last month, and more than 500 records came in last month. Then your live table will exceed the optimum number of rows.

Lets look at what we are trying to achieve.

DELETE * FROM table LIMIT 500;

To do this we first have to take a table structure in account

CREATE  TABLE IF NOT EXISTS `mytable` (
  `Id` BIGINT(20) NOT NULL,
  `dateReg` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`)
)
ENGINE = InnoDB

Once we create the table we want to populate it.

INSERT INTO mytable(1, NULL);
INSERT INTO mytable(2, NULL);
....
INSERT INTO mytable(1000, NULL);

And then we use the delete query in such a way

DELETE FROM mytable WHERE Id NOT IN 
    (SELECT Id FROM mytable ORDER BY dateReg LIMIT 500);

Hope this helps