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, Raymond A Jr <[EMAIL PROTECTED]> wrote:
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 rows affected (36.38 sec) Records: 7501376 Duplicates: 0 Warnings: 0 Next I want to delete all rows from the table data when data.cid = sidtemp.cid So I started the following command on Jul 5 at 16:44 GMT: DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid It is now Jul 7 19:56 GMT. I had forgotten how long it takes to run this delete command as I recall it takes 15-20days on just one database. I have two(2) Databases with the same schema. The databases are live now and Usually without executing this delete mysql uses between 0-10% Of the CPU. The delete is causing the mysql to use between 98-99% of the CPU. Any ideas on what I can do to speed up the Delete? Thank you Raymond
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
