Yes, I agree, it looks like there are indexes on the columns in question in both tables.
As to when to use a temporary table - I haven't got a clear answer for you. I wrote an app once that used temp tables, and it gained quite a speed advantage due to my requirement to first insert a bunch of data, then update it, then move it into a "real" table. It was much faster overall than running the same operations against the "real" table. However, the use of actual temporary tables became problematic during replication from time to time (replication would break sometimes), so I scrapped that and instead created a permanent MyISAM table that I treated like a temp table. Dan On 7/7/06, Jacob, Raymond A Jr <[EMAIL PROTECTED]> wrote:
-----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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
