title: What is the best way to delete old rows from MySQL on a rolling basis?
body: <p>I find myself wanting to delete rows older than (x)-days on a rolling basis in a lot of applications. What is the best way to do this most efficiently on a high-traffic table?</p>
  
  <p>For instance, if I have a table that stores notifications and I only want to keep these for 7 days. Or high scores that I only want to keep for 31 days.</p>
  
  <p>Right now I keep a row storing the epoch time posted and run a cron job that runs once per hour and deletes them in increments like this:</p>
  
  <pre><code>DELETE FROM my_table WHERE time_stored &lt; 1234567890 LIMIT 100
  </code></pre>
  
  <p>I do that until mysql_affected_rows returns 0.</p>
  
  <p>I used to do it all at once but that caused everything in the application to hang for 30 seconds or so while INSERTS piled up. Adding the LIMIT worked to alleviate this but I'm wondering if there is a better way to do this.</p>