.
- Original Message -
From: Jacob, Raymond A Jr [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Sunday, July 09, 2006 10:36 PM
Subject: How does one speed up delete-Again
I started the operation below on Friday at 1300hrs EST
DELETE data
FROM data, event
WHERE data.cid = event.cid
Could the problem the Locked data table in this case?
mysql show processlist;
+-+---+---+---+-+---
-+--+---
---+
| Id | User | Host
It appears that every time I start query the event or the data table
gets Locked.
Could this have any affect on why it takes so long to delete records.
Grasping at straws,
Thank you,
Raymond
mysql show processlist;
+-+---+---+---+-
I started the operation below on Friday at 1300hrs EST
DELETE data
FROM data, event
WHERE data.cid = event.cid
AND event.timestamp 2006-05-01
It is now Sunday 22:00hrs EST and the operation is still running.
Question: Should it take this long to delete 7.5 million records from a
4.5GB
Env: Freebsd 6.0
MySql 4.1.18
Mem: 1GB(?) can not tell without rebooting
Disk Avail: 4GB
Problem: the table data is 4.5GB.
I created a temporary table sidtemp in the database snort by typing:
CREATE TEMPORARY TABLE sidtemp
SELECT cid FROM event
WHERE timestamp '2006-05-01';
Query OK, 7501376
Raymond, I would expect that adding an index on 'cid' column in your
'sidtemp' table would help quite a bit.
Out of curiousity, why use a temp table in this situation? Why not
DELETE data
FROM data, event
WHERE data.cid = event.cid
AND event.timestamp 2006-05-01
Dan
On 7/7/06, Jacob,
-Original Message-
From: Dan Buettner [mailto:[EMAIL PROTECTED]
Sent: Friday, July 07, 2006 15:48
To: Jacob, Raymond A Jr
Cc: mysql@lists.mysql.com
Subject: Re: How does one speed up delete.
Raymond, I would expect that adding an index on 'cid' column in your
'sidtemp' table would
Subject: Re: How does one speed up delete.
Raymond, I would expect that adding an index on 'cid' column in your
'sidtemp' table would help quite a bit.
Out of curiousity, why use a temp table in this situation? Why not
Dan:
I had erroneously assumed that the delete would delete rows from data
