Delete Rows So i have a max Row Limit

Question

Here is what im trying to do explained in a query

DELETE FROM table ORDER BY dateRegistered DESC LIMIT 1000 *

I want to run such query in a script which i have already designed. Every time it finds older records that are 1001th record or above it deletes

So kinda of setting Max Row size but deleting all the older records.

Actually is there a way to set that up in the CREATE statement.

Therefore: If i have 9023 rows in the database, when i run that query it should delete 8023 rows and leave me with 1000

Answer 1

The only way that immediately occurs to me for this exact job is to do it manually.

First, get a lock on the table. You don't want the row count changing while you're doing this. (If a lock is not practical for your app, you'll have to work out a more clever queuing system rather than using this method.)

Next, get current row count:

SELECT count(*) FROM table

Once you have that, you should with simple maths be able to figure out how many rows need deleting. Let's say it said 1005 - you need to delete 5 rows.

DELETE FROM table ORDER BY dateRegistered ASC LIMIT 5

Now, unlock the table.

---

If a lock isn't practical for your scenario, you'll have to be a bit more clever - for example, select the unique ID of all the rows that need deleting, and queue them for gradual deletion. I'll let you work that out yourself :)

Answer 2

If you have a unique ID for rows here is the theoretically correct way, but it is not very efficient (not even if you have an index on the dateRegistered column):

DELETE FROM table
WHERE id NOT IN (
    SELECT id FROM table
    ORDER BY dateRegistered DESC
    LIMIT 1000
)

I think you would be better off by limiting the DELETE directly by date instead of number of rows.

I don't think there is a way to set that up in the CREATE TABLE statement, at least not a portable one.