Menu
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?
459
To delete all rows older than 30 days, you need to use the DELETE with INTERVAL. Use < now() i.e. less than operator to get all the records before the current date.
Let say you want to delete data that over 31 days old. CREATE TABLE delete_keys SELECT id FROM my_table WHERE 1=2; INSERT INTO delete_keys SELECT id FROM ( SELECT id FROM my_table WHERE time_stored < (UNIX_TIMESTAMP() - 2678400) ORDER BY time_stored ) A LIMIT 100; ALTER TABLE delete_keys ADD PRIMARY KEY (id); DELETE B.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
