MySQL Purge Script (PHP?)

[David Nelson]

Hey guys,

 

I manage a large service that has dozens of MySQL read/writes coming in every second - one of our tables now has over 5+ million rows (and about 500MB of data), and the structure has three columns:

 

userid | data | timestamp

 

"userid" is a unique identifier for the user responsible for the data. Some users have thousands and thousands of entries -- others have only a few.

 

For those users with greater than 600 entries, I'd like to keep only their most recent 600 entries. Luckily we store a timestamp!

 

So, from a programming standpoint my thought was that I would have to loop through every single row, identify "duplicate" userids, put them into an array with their respective data and timestamps, and eventually purge those arrays down to 600 rows only, etc..

 

You can see that this would be pretty complicated, so I thought I would ask if any of you know of any good purging scripts / something that might help with this.

 

As advanced as this is, it can't be uncommon.

 

Thanks - let me know if you need any clarification!

 

David

[PFMaBiSmAd]

The following is some basic code that does what you state, but using a temporary table in the database. I tested this using a small set of data. You should test it first with an offline copy of your database to see if it has any performance problems and also have a known good backup of your online database before you attempt to run it online -

 

// create the temp table
$query = "CREATE TEMPORARY TABLE temp_table (userid INT, timestamp INT)";
mysql_query($query);

$max = 600; // the maximum number of rows to keep for each user

// get a list of userid's that have more than $max rows -
$query = "SELECT userid, count(*) AS cnt FROM your_table GROUP BY userid HAVING cnt > $max";
$result = mysql_query($query);

// get the $max'th row for each of those users into a temp table (rows start at zero so this actually gets the first row to be deleted)
while($row = mysql_fetch_assoc($result)){
$query = "INSERT INTO temp_table (userid,timestamp)
	SELECT your_table.userid, your_table.timestamp
	FROM your_table WHERE userid = {$row['userid']} ORDER BY timestamp DESC LIMIT $max,1";
mysql_query($query);
}

// delete the rows with the same and older (smaller) timestamps
$query = "DELETE t1 FROM your_table t1 INNER JOIN temp_table t2
WHERE t1.userid=t2.userid AND t1.timestamp <= t2.timestamp";
mysql_query($query);

[David Nelson]

Genius method. 

 

That took the DB to a much more manageable 1.1 million rows.

 

Thanks a ton!

 

David