-----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 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 and event. DELETE data FROM data, event WHERE data.cid = event.cid AND event.timestamp < "2006-05-01" I just stopped my previous query. I am running the above now. I used a temporary table because I thought I only needed the table to hold the events.cid's temporarily that I wished to delete from the data table. Can you tell when I should use temporary tables. Below I believe command below demonstrates that an index exists on data and event? mysql> show index from data; +-------+------------+----------+--------------+-------------+---------- -+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+---------- -+-------------+----------+--------+------+------------+---------+ | data | 0 | PRIMARY | 1 | sid | A | 3 | NULL | NULL | | BTREE | | | data | 0 | PRIMARY | 2 | cid | A | 9678480 | NULL | NULL | | BTREE | | +-------+------------+----------+--------------+-------------+---------- -+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec) mysql> show index from event; +-------+------------+----------+--------------+-------------+---------- -+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+---------- -+-------------+----------+--------+------+------------+---------+ | event | 0 | PRIMARY | 1 | sid | A | NULL | NULL | NULL | | BTREE | | | event | 0 | PRIMARY | 2 | cid | A | 14389173 | NULL | NULL | | BTREE | | | event | 1 | sig | 1 | signature | A | NULL | NULL | NULL | | BTREE | | | event | 1 | time | 1 | timestamp | A | NULL | NULL | NULL | | BTREE | | +-------+------------+----------+--------------+-------------+---------- -+-------------+----------+--------+------+------------+---------+ 4 rows in set (0.00 sec) Thank you, raymond 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]
