Delete old rows in table if maximum exceeded

Question

I have insert in table any time when users open any post on my site, in this way im get real time 'Whats happend on site'

mysql_query("INSERT INTO `just_watched` (`content_id`)  VALUES ('{$id}')");

but now have problem because have over 100K hits every day, this is a 100K new rows in this table every day, there is any way to limit table to max 100 rows, and if max is exceeded then delete old 90 and insert again or something like that, have no idea what's the right way to make this

my table just_watched

ID - content_id

ID INT(11) - AUTO_INCREMENT
content_id INT(11)

My idea would be to count all the rows in the the table and, if it's more than 100, you delete the oldest before inserting the new one. — George Marques, Sep 15 '13 at 18:00
Write a script that removes all records older than today. And run it on cron everyday for example. — u_mulder, Sep 15 '13 at 18:01

score 1 · Accepted Answer · edited May 23 '17 at 12:20

Easiest way that popped into my head would be to use php logic to delete and insert your information. Then every time a user open a new post you would then add the count the database. (this you are already doing)

The new stuff comes here

Enter a control before the insertion, meaning before anything is inserted you would first count all the rows, if it does not exceed 100 rows then add a new row.

If it does exceed 100 rows then you before inserting a new row you, first do a delete statement THEN you insert a new row.

Example (sudo code) :

$sql = "SELECT COUNT(*) FROM yourtable";
$count = $db -> prepare($sql);
$count -> execute();

if ($count -> fetchColumn() >= 100) { // If the count is over a 100
    ............... //Delete the first 90 leave 10 then insert a new row which will leave you at 11 after the delete.
} else {
    .................. // Keep inserting until you have 100 then repeat the process
}

More information on counting here. Then some more information on PDO here.

Hopefully this helps :)

Good luck.

Also information on how to set up PDO if you haven't already.

What I would do? :

At 12:00 AM every night run a cron job that deletes all rows from the past day. But thats just some advice. Have a good one.

score 0 · Answer 2 · answered Sep 15 '13 at 18:03

0

Use this query for deleting old rows except last 100 rows:

DELETE FROM  just_watched where 
ID not in (SELECT id fromjust_watched order by ID DESC LIMIT 100)

You can run it by CRON in every n period where (n= hours, or minutes, or any)

answered Sep 15 '13 at 18:03

Jason OOO

3,567
2
25
31

score 0 · Answer 3 · answered Sep 15 '13 at 18:04

$numRows = mysql_num_rows(mysql_query("SELECT ID FROM just_watched"));
if ($numRows > 100){
mysql_query("DELETE FROM just_watched LIMIT 90");
}
mysql_query("INSERT INTO `just_watched` (`content_id`)  VALUES ('{$id}')");

I guess this should work fine.

score 0 · Answer 4 · answered Sep 15 '13 at 18:04

You can get the number of rows in your table with:

$size = mysql_num_rows($result);

With the size of the table, you can check, if it's getting to big, and then remove 90 rows:

// Get 90 lines of code
$query = "Select * FROM just_watched ORDER BY id ASC LIMIT 90";
$result = mysql_query($query);
// Go through them
while($row = mysql_fetch_object($result)) {
  // Delete the row with the id
  $id = $row['id'];
  $sql = 'DELETE FROM just_watched
    WHERE id=$id';
}

Another way would be to just delete an old row if you add a new row to the table. The only problem is, that if something get's jammed, the table might get to big.

score 0 · Answer 5 · answered Sep 15 '13 at 18:07

You may use

DELETE FROM just_watched ORDER BY id DESC LIMIT 100, 9999999999999999;

So, it'll delete all the rows from the offset 100 to a big number (for end of the tables). if you always run this query before you insert new one then it'll do the job for you.

Delete old rows in table if maximum exceeded

5 Answers5