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