How to speed up this DELETE / LEFT JOIN mysql query?

Question

On a mysql database (running on Linux) I do the following query:

delete m,i from Table1 AS m 
LEFT JOIN 
Table2 AS i 
ON m.GUID = i.OutId 
WHERE m.Direction='in'
AND m.Id<4294967296

which takes a time of almost 2 minutes! There were 458 entries in Table1 and 5659 entries in Table2.

Why does this query consume so much time? The COUNT query to count the elements in Table2 just used 0.24 seconds for comparison.

How can I speed up this query? Which parts are the time consuming?

Additional information:

• I am no sql expert
• I tried to add indexes to the table by running alter table Table add index (GUID); (and similar commands for the other columns), but it seems it has no effect on the time consumption. Am I doing something wrong here?
• The 'optimization' or whatever must be done without altering the table structure.

Answer 1

How to speed up this DELETE / LEFT JOIN mysql query?

Your time of three minutes seems really slow. My guess is that the id column is not being indexed properly. If you could provide the exact table definition you're using that would be helpful.

I created a simple python script to produce test data and ran multiple different versions of the delete query against the same data set. Here's my table definitions:

drop table if exists a;
create table a
 (id bigint unsigned  not null primary key,
  data varchar(255) not null) engine=InnoDB;

drop table if exists b;
create table b like a;

I then inserted 100k rows into a and 25k rows into b (22.5k of which were also in a). Here's the results of the various delete commands. I dropped and repopulated the table between runs by the way.

mysql> DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE a.id=b.id);
Query OK, 22500 rows affected (1.14 sec)

mysql> DELETE FROM a USING a LEFT JOIN b ON a.id=b.id WHERE b.id IS NOT NULL;
Query OK, 22500 rows affected (0.81 sec)

mysql> DELETE a FROM a INNER JOIN b on a.id=b.id;
Query OK, 22500 rows affected (0.97 sec)

mysql> DELETE QUICK a.* FROM a,b WHERE a.id=b.id;
Query OK, 22500 rows affected (0.81 sec)

All the tests were run on an Intel Core2 quad-core 2.5GHz, 2GB RAM with Ubuntu 8.10 and MySQL 5.0. Note, that the execution of one sql statement is still single threaded.