How do I expire rows based on a lookup table of expiry times?

Question

If I have two tables:

items
    Id VARCHAR(26)
    CreateAt bigint(20)
    Type VARCHAR(26)

expiry
    Id VARCHAR(26)
    Expiry bigint(20)

The items table contains when the item was created, and what type it is. Then another table, expiry, is a lookup table to say how long certain types should last for. A query is run every day to make sure that items that have expired are removed.

At the moment this query is written in our app, as programming code:

for item in items {
    expiry = expiry.get(item.Type)
    if (currentDate() - expiry.Expiry > item.CreateAt) {
        item.delete()
    }
}

This was fine when we only had a few thousand items, but now we have tens of millions it takes a significant amount of time to run. Is there a way to put this into just an SQL statement?

Answer 1

Assuming all date values are actually UNIX timestamps, you could write a query such as:

SELECT * -- DELETE
FROM items
WHERE EXISTS (
    SELECT 1
    FROM expiry
    WHERE expiry.id = items.type
    AND items.CreateAt + expiry.Expiry < UNIX_TIMESTAMP()
)

Replace SELECT with DELETE once you're sure that the query selects the correct rows.