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;
+-+---+---+---+-
Could the problem the Locked data table in this case?
mysql> show processlist;
+-+---+---+---+-+---
-+--+---
---+
| Id | User | Host
e tables can provide considerable amount of scalability.
- Original Message -
From: "Jacob, Raymond A Jr" <[EMAIL PROTECTED]>
To:
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
DELE
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
T
: 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 help quite a bit.
Out of curiousity, why use a temp table in this situation? Why not
Dan:
I had erroneo
-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
'sidte
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, Raym
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
