***BOGO*** Re: delete takes 4 hours

["Mike Bolton" <palefacerocker@xxxxxxxxx>]

Thanks Cor, I wasn't aware of the truncate command.  My delete operation has
now been reduced from 4 hours to an almost instantaneous result.  I'm still,
however, having a problem with my load data infile statement.  It takes 43
min 8.71 sec on the production machine and 7 minutes on the dev machine,
with the same schema.
I'm about to try uninstalling and reinstalling mysql.  I'm
using 5.1.12-beta-log on the development machine, so I want the production
machine to be compatible, although I'm not using anything out of the
ordinary in my queries, so I don't think there should be a problem if I
downgrade to 5.0 (perhaps it's more stable?).  In any case, when installing
from source, are there any special optimization flags or anything that I may
need to be aware of?  I'm thinking that maybe when I installed mysql on the
production machine, I may have missed some option or left some debug option
on or something.  I just can't think why else there would be such a large
difference in query execution speed between the dev and production machines.
 Thanks again for your help

Mike


On Tue, May 27, 2008 at 6:44 AM, C.R.Vegelin <cr.vegelin@xxxxxxxxx> wrote:

> ----- Original Message ----- From: "Mike Bolton" <palefacerocker@xxxxxxxxx
> >
> To: <mysql@xxxxxxxxxxxxxxx>
> Sent: Tuesday, May 27, 2008 8:47 AM
> Subject: delete takes 4 hours
>
>
>
>  I've got two machines running mysql, a development box and a production
>> box.
>> I have a database table with 795755 rows on both machines.  The schema is
>> the same.  When I execute a "delete from my_large_table" statement, it
>> takes
>> 9 minutes on the dev box and 4 hours on the production machine.  Needless
>> to
>> say, this is not usable in a production environment, especially when I
>> have
>> to delete and load the large table every single day (the table contains a
>> list of products as given by a distributor).  The configurations of the
>> two
>> machines are as follows:
>>
>> dev box:
>> FreeBSD dev 6.1-RC FreeBSD 6.1-RC #7
>> 512 Megs RAM
>> Pentium 4
>> MySQL: 5.1.12-beta-log FreeBSD port: mysql-server-5.1.12
>>
>>
>> production box:
>> CentOS  2.6.9-55.0.12.EL
>> Intel 2.4 Ghz P4 Celeron
>> 512 Megs RAM
>> MySQL: 5.1.22-rc-community
>>
>>
>> Can anyone tell me why this could possibly be taking so long?  Or where
>> else
>> I should look to try to solve this issue?  Your help is greatly
>> appreciated
>> since I'm at a complete loss here.  Thanks,
>>
>> Mike
>>
>
> Hi Mike,
>
> In case all records must be deleted, see:
> http://dev.mysql.com/doc/refman/5.1/en/truncate.html
>
> HTH, Cor
>
>