Delete all entries over 15 minutes old

Question

I have a table which gets around 10-15k entries per minute. Each one is marked with the current timestamp upon entry. The table is a MEMORY table, since losing data is not a concern.

Every minute, I have a script which runs the following query:

DELETE FROM tracker WHERE post_time < DATE_SUB(NOW(), INTERVAL 15 MINUTE)

This query is taking about 1-2 seconds to run, which isn't bad, but it seems that this type of query (deleting everything older than X) should be able to perform much faster when being run against a MEMORY table. It also has a corresponding spike to the CPU which sticks out like a sore thumb every minute.

Are there any optimizations I can do to my query to run this query more efficiently?

Answer 1

As always, you should view the query plan, and post it here. You do that by issuing EXPLAIN DELETE FROM tracker WHERE post_time < DATE_SUB(NOW(), INTERVAL 15 MINUTE)

Now, the problem is likely that the DELETE query can't use an index, and have to loop through all of your rows.

Even if you already have an index on post_time , it will likely not be used, as by default indexes on MEMORY tables are hash indexes. Hash indexes can only be used for equality checks, and not ranges such as post_time < DATE_SUB(NOW(), INTERVAL 15 MINUTE)

Create a BTREE index on your post_time column,

CREATE INDEX post_time_idx ON tracker (post_time) USING BTREE;