limit a table to ... rows

philippo

how do I limit a table to let's say 1000 rows?
What's the DELETE statement to delete all rows except the 1000 most recent ones?
I don't want to delete by date, I just want to delete anything that goes over 1000 rows.

thanx
Philippo

[deleted]

I don't know of any database that can limit the number of rows in a database as a feature.
I guess you could create a key or something..

But that's much more complex than just running
a query like :
SELECT count(*) FROM table;

and seeing if that says 1000.

Before you can delete you need to know what to delete.
If you want to delete "everything but the last 1000 records" you'll need to be able to find the records that are not part of the final 1000.

You could create a comma-seperated string of the ID's of all the 1000 records you want to keep, and then
DELETE FROM table WHERE ID not in ($string);

$string would be (1,3,4,5,6,7,6 etc untill 1000)

philippo

hey vincent
thanx for your idea. I thought this would be much easier!
I don't think your "DELETE FROM table WHERE id NO IN ($string)" is that efficient if $string contains 1000 or even 2000 values.

in the meantime I figured out some other solution:

$sql = "SELECT logid FROM logs;";
$res = mysql_query($sql);
$del_rows = mysql_affected_rows()-$lim;
if ($del_rows > 0) {
$sql = "DELETE FROM logs LIMIT ".$del_rows.";";
$res = mysql_query($sql);
}

I set $lim to e.g. 1000 and subtract that number from the total number of rows. Then I limit the DELETE statement by that number.

What do you think about that solution? Is there any risk to delete wrong data?

Philippo

[deleted]

Your solution should work,
but remember that the LIMIT in a DELETE query is only for MySQL, not for other databases.

BTW, you don't need to use affected_rows() to get the number of rows to delete,
a simple count(*) would be much faster than selecting the whole column.

philippo

hey Kirk
where did you find that information in the mySQL doc?
I made several tests on my table and it always worked. It always starts deleting from top (from the oldest entries).

If I cannot use LIMIT there must be another efficient way... It can't be that hard!! ?

icaro

hi, i have a similar problem (in a newer post); i want to have only 50 lines in a table with a serial key.. an answer for your problem can be:

$last = "SELECT MAX('yourkey') FROM table";
$todel = $last - 50;
$query_del = "DELETE FROM TABLE WHERE
yourkey < $todel";

the new problem is:
and if i have big trafic and that key get higher and higher!, can it be a problem?
can i rename the serial key? example: update sometable set yourkey = '1';

i think there is no way to do it only with mysql...

kparker

Is there any risk to delete wrong data?

Sure there is. At least the way I read the mysql documentation, it makes absolutely no promises regarding which particular rows it will delete to fulfill hte LIMIT count.

kparker

where did you find that information in the mySQL doc?

I just looked in the online manual on their website under DELETE.

I made several tests on my table and it always worked. It always starts
deleting from top (from the oldest entries).

That's fine as long as you can freeze your current version. The risk is, since the specific behavior you want is neither part of the SQL standard nor something the MySQL guarantees, they're free to change it to work in some other way.

philippo

hey Kirk
thanks for your help
I'm trying to figure out another solution...
what I got is a table that contains a column called 'logid'
this ID I get from PHP:
$logid=uniqid("");

so, I could sort by logid and then delete each row separately:

$sql = "SELECT logid FROM t_logs ORDER BY logid;";
$res = mysql_query($sql);
$del_rows = mysql_affected_rows()-$dellog_lim;
$i = 0;
while (($row = mysql_fetch_array($res)) && ($i < $del_rows)) {
$sql2 = "DELETE FROM t_logs WHERE logid = '$row[0]';";
$res2 = mysql_query($sql2);
$i += 1;
}

well, it works, but I would like to hear your opinion about that solution. Would be much slower, but more secure. - unless uniqid("") does really return a random value (check http://www.php.net/manual/en/function.uniqid.php).

thanks
Philippo