What's the best way to 'prune' a table?

schwim

Hi there guys,

I'm tracking http requests, and everything seems to be working dandy. In an effort to save space, I've set it to update a hitcounter on an IP if found, instead of writing a new table entry each time.

Regardless, this file is going to get big QUICK 😃, so I need to find a way to keep it at a manageable size, like 500 entries maybe.

What's the best way to go about this? I figure I need to do the pruning at the same time as an entry, so at 500, it seems like it will remove one every time it adds one, so maybe the command needs to be integrated into that portion of the script?

/* If it's 0, then we're seeing this bot for the first time. */
if ($habitual == 0) {

$tmestamp = time();

/* Append bad bot address data to blacklist database. */
$sql = "INSERT INTO ".$zapprefix."tracker (id, hits, remote_addr, date, request_method, request_uri, server_protocol, http_referer, http_user_agent) VALUES ('NULL', '1', '$REMOTE_ADDR', '$tmestamp', '$REQUEST_METHOD', '$REQUEST_URI', '$SERVER_PROTOCOL', '$HTTP_REFERER', '$HTTP_USER_AGENT')";

$result = mysql_query($sql) or die('MySQL error: ' . mysql_error() . '<hr/>' . $sql);


}else{

any suggestions would be greatly appreciated.

thanks,
json

brianadkins

I'd probably

do your insert
perform a select to get the top 500 IPs based on hits as well as the number of hits for each.
Use the smallest 'hits' result from #2 and perform a delete WHERE hits < that number

Using this method, you won't always have exactly 500 rows, but in the ballpark.

schwim

Well, if I'm understanding you correctly, that wouldn't work.

I have 568 rows right now. If I add all the hits up, I've got over 3,000. Some IP's have hit 1-5 and some have hit 100's(crawlers). Deleting by number of hits wouldn't work.

Any help would be appreciated 🙂

thanks,
json

brianadkins

Sorry, guess I misinterpreted your question. I thought you wanted a log of the visitors who visit most often. Re-reading your question now, I'm guessing you want to keep only the last 500 visitors?

If that's the case, then your 'hits' field will actually represent the total number of hits from a given IP since the last time that the IP was purged from and then re-added to the table (and not the actual total historical hits from that IP).

My approach would be the same, just grabbing the 500 'latest' entries in step 2 instead.

do your insert
perform a select to get the latest 500 visitor IDs (ordering results by decending date and limiting results to 500 rows) and the date for each.
Use the earliest date retrieved in #2 and perform a delete WHERE date < that value

schwim

Hi there Brian,

Thanks very much for your reply again.

I think I understand what you mean, but I don't know how to format those commands. Would you happen to be able to give an example?

thanks for your time,
json

schwim

Hey there guys,

Any thoughts at all? I disabled the script until I can figure out how to do this, as the table is growing at an alarming rate.

thanks,
json

Sxooter

something like:

select date from table order by date limit 1 offset 500 -- this will get you a date. assign it as cutoff
delete from table where date > '$cutoff'

test it to be sure. But that should work to get rid of entries < 500 by age. I think. Maybe...

EDIT: Had the < facing the wrong way... it would have deleted all your newest entries.

MarkR

If you want a constant-time implementation, then:

Select the 500 newest rows and copy them into a newly create table with the same schema
Drop the original table
Rename the new table to the original one

Of course, ensure that you have an index on the date.

Mark

schwim

Hey there guys, and thanks very much for your responses.

@ Sxooter:

Would this be how I would go about it?

$sql = "SELECT date from ".$zapprefix."tracker ORDER BY date LIMIT 1 OFFSET 500";
$result = mysql_query ($sql);
while ($row = mysql_fetch_assoc ($result)) {
	$cutoff = $row['date'];
}

$sql = "DELETE FROM ".$zapprefix."tracker where date > '$cutoff'";

Wouldn't deleting and recreating a table be a little much for a process that would get run on each page load on a fairly busy site?

thanks,
json

schwim

hey there again guys,

I duped my table and played around with it, and it's working, but I wanted to make sure that it's as efficient as it can be, as it's getting called on every page load:

<?php

include("config.php");

$sql = "SELECT date from tracker2 ORDER BY date LIMIT 1 OFFSET 500";
$result = mysql_query ($sql);
while ($row = mysql_fetch_assoc ($result)) {
	$cutoff = $row['date'];
}

$sql = "DELETE FROM tracker2 WHERE date > '$cutoff'";
$result = mysql_query ($sql);

?>

Any suggestions are welcome.

thanks,
json

[EDIT]Thinking about it, would it save appreciable resources if I had it check the table count until it got to 600 and then prune it to 500? This would only add a single row count until it got to the ceiling, and then would start over again. Is there a better way, or is it even necessary?[/EDIT]

Sxooter

1: reverse your > to a < up there

2: rather than counting the table each time, why not do something like:

if (rand(1,100)==1){
  // prune table here
}

So that it only runs every 100 times or so?

schwim

Hi there Sxooter,

Changing the gt to lt got me on the right side of the files to be saved, but now it's deleting 500 rows instead of saving 500 rows 🙂

for instance, I had 1136 rows. I ran the script with the gt changed to an lt, and now I have 636 rows left in the database.

Do I need to change the order of the first statement from descending to ascending? If I do, how do I tell it to order it by date and order by ascending?

thanks,
json

schwim

think I got it fixed:

$sql = "SELECT date from ".$zapprefix."tracker2 ORDER BY date DESC LIMIT 1 OFFSET 499";

thanks very much for your help,
json