I have a table populated very often with new entries. Records older than 2 days get deleted every few minutes with:
DELETE FROM ip_log WHERE timestamp < NOW() - INTERVAL 2 DAY
This is the only query by timestamp, the other queries on this table are on the IP which is the PK (I'm interested in knowing if this user is new or already had activity recently)
The index on timestamp should be ASC or DESC?
The index structure is just as fast going backwards as it is going forwards, so your query won't benefit from reversing the direction of the tree.
But especially for timestamps, but really for any key that increments in one direction, I would set it up so that new (larger) values are at the end, so leave it at ASC order. This will reduce fragmentation of the index since regardless of your key order, new values are written at the end unless there is sufficiently low fill factor to support out of order inserts.
