title: Deleting old records - MySQL
body: <p>I currently am looking for a solution to a basic problem I have: the deletion of old records.</p>
  
  <p>To explain the situation, I have a table, which I'll call table1, with a reduced number of records. Usually it stays empty, as it is used to relay messages. These messages are read within two seconds of being added to the database, and deleted so that they aren't read again.</p>
  
  <p>However, if one of the clients supposed to receive the messages from table1 goes offline, several messages can become pending. Sometimes hundreds. Sometimes thousands, or even hundreds of thousands, if not more.</p>
  
  <p>Not only does this hurt the client's performance, which will have to process a huge amount of messages, it also hurts the database's which is kept in memory and should keep a minimal amount of records.</p>
  
  <p>Considering the clients check for new messages every second, what would be the best way to delete old records? I've thought about adding timestamps, but won't that hurt the performance: the fact that it has to calculate timestamps when inserting? I've tried it out, and all those queries ended up in the slow queries log.</p>
  
  <p>What would the best solution be? I've thought about something like checking if the table was altered in the past 5 seconds, and if not, we can be safe that all messages that should be relayed have been relayed already, and it can be wiped. But how can this be done?</p>
  
  <p>I've thought about events running every couple of minutes, but I'm not sure how to implement something that would have no (or meaningless) impact on the select/insert/delete queries.</p>
  
  <p>PS: This situation arrives when I noticed that some clients were offline, and there were 8 million messages pending.</p>
  
  <p><strong>EDIT :</strong></p>
  
  <p>I had forgotten to mention that the storage engine is MEMORY, and therefore all records are kept in RAM. That's the main reason I want to get rid of these records: because millions of records which shouldn't even be there, being kept in RAM, has an impact on system resources.</p>
  
  <p>Here is an extract from the error log:</p>
  
  <pre><code># Query_time: 0.000283  Lock_time: 0.000070 Rows_sent: 0  Rows_examined: 96
  SET timestamp=1387199997;
  DELETE FROM messages WHERE clientid='100';
  [...]
  # Query_time: 0.000178  Lock_time: 0.000054 Rows_sent: 0  Rows_examined: 96
  SET timestamp=1387199998;
  DELETE FROM messages WHERE clientid='14';
  </code></pre>
  
  <p>So I guess they do have a quite small delay, but is it in any way meaningful in MySQL? I mean, in "real life", 0.0003 could be completely ignored due to its insignificance, can the same be said about MySQL and connections with approximately 10ms ping?</p>