You got it, however MySQL is horrible with < than arguments on a query. I'd
recommend that you
use a between;
SELECT @l_id=MIN(ID) FROM tbl; SELECT @m_id=MAX(ID) FROM tbl WHERE
LastUpdate>@time; DELETE FROM tbl WHERE ID between @l_id and @m_id;
Also, when I'm deleting out of our system, usually in the order of many
millions at a time, I
split the deletion into chunks.
"Jeremy Howard" <j+google@howard.fm> wrote in message
news:279db04c.0408162123.617a1702@posting.google.c om...[color=blue]
> "Steve McGregory" <stevemcgregory@yahoo.com> wrote in message[/color]
news:<cB7Uc.8083$_84.455@newssvr22.news.prodigy.co m>...[color=blue][color=green]
> > I too discovered that InnoDB tables are horrible when it comes to[/color][/color]
deletions.[color=blue][color=green]
> >
> > I have found that increasing the key_buffer_size will speed up deletes[/color][/color]
on[color=blue][color=green]
> > very large tables. Also, the number of indexes on a table directly[/color][/color]
affects[color=blue][color=green]
> > the performance of the delete.[/color]
>
> OK I'll try that - thanks.
>[color=green]
> > Another thing to be concerned about is data file fragmentation and slow
> > overall performance for InnoDB tables. If you have a table that
> > continuously grows and you want to delete the oldest records, oldest is
> > maintained by insertion time, then you can use an auto_increment column[/color][/color]
as[color=blue][color=green]
> > your table index. The count of records in such a table is very fast to
> > compute, and deletion by this index can be fast as well.[/color]
>
> That's a great idea - exactly the situation I'm in. I'll give that a
> try - something like 'SELECT @m_id=MAX(ID) FROM tbl WHERE
> LastUpdate>@time; DELETE FROM tbl WHERE ID<@m_id;' I guess...
>[color=green]
> > "Jeremy Howard" <j+google@howard.fm> wrote in message
> > news:279db04c.0408152237.60fa6b4@posting.google.co m...[color=darkred]
> > > I am finding delete queries on large InnoDB tables very slow - are
> > > there ways to speed this up?
> > >
> > > I have a table with about 100 million rows:
> > >
> > > I am trying to delete just a few of these rows (the following select
> > > takes a couple of seconds):
> > >
> > > > SELECT count(*)
> > > -> FROM UserSnap
> > > -> WHERE LogDate<now() - INTERVAL 750 DAY;
> > > +----------+
> > > | count(*) |[/color]
> > +----------+[color=darkred]
> > > | 308969 |
> > > +----------+
> > >
> > > > DELETE FROM UserSnap WHERE LogDate<now() - INTERVAL 750 DAY;
> > >
> > > That delete query takes hours to run. The structure of the table is:
> > >
> > > > desc UserSnap;[/color]
> > +----------+-------------+------+-----+---------------------+-------+[color=darkred]
> > > | Field | Type | Null | Key | Default | Extra |[/color]
> > +----------+-------------+------+-----+---------------------+-------+[color=darkred]
> > > | LogDate | datetime | | PRI | 0000-00-00 00:00:00 | |
> > > | Period | tinyint(4) | | PRI | 0 | |
> > > | UserName | varchar(50) | | PRI | | |
> > > | RateType | varchar(50) | | PRI | default | |
> > > | Rate | float | YES | | NULL | |
> > > +----------+-------------+------+-----+---------------------+-------+
> > >
> > > Any suggestions on why this is slow, and what to do about it?[/color][/color][/color]
