--- "David T. Ashley" <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I'm a beginning MySQL user ...
>
> I have a table of log entries. Over time, the entries could grow to
> be
> numerous. I'm like to trim them to a reasonable number.
>
> Is there a query that will, say, trim a table down to a million rows
> (with
> some sort order, of course, as I'm interested in deleting the oldest
> ones)?
>
> The sorting isn't a problem. I've just never seen an SQL statement
> that
> will drop rows until a certain number remain ...
>
> Thanks, Dave.
>
There are two ways to do this but this is the simplest:
(this should set the value of @lastID to whatever is the 100001st
oldest ID)
SELECT @lastID:= ID, @lastDate:= datecolumn
FROM yourtablename
ORDER BY datecolumn desc, id desc
LIMIT 100000,1;
Now, assuming that there are several records within the same second
(very possible depending on your traffic) and that you want to limit
the database to exactly 100000 rows (see previous query), then this
should work for you:
DELETE FROM yourtablename
WHERE datecolumn < @lastDate
or (datecolum = @lastDate
AND id < @lastID);
Let us know how it works...
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
