0

I am no mysql expert at all but I need to figure out the slowness of following query:

DELETE m,i from Table1 AS m 
LEFT JOIN 
Table2 AS i 
ON m.id = i.id 
WHERE m.state='in'

Table1 consist of 600 entries (SELECT COUNT(*) from Table1 took about 0.00 seconds) and Table2 of 202 entries (SELECT COUNT(*) from Table2 took about 0.00 seconds). However, the above JOIN DELETE query takes about 0.77 seconds.

Is there any way to speed up this DELETE query without changing the table definition?

I tried to 'index' the columns in question by applying the following three queries before the DELETE query: 

alter table Table1 add index(id);
alter table Table1 add index(state);
alter table Table2 add index(id);

but the above JOIN DELETE query still takes about the same time! So indexing columns does not seem to have any effect on the effectiveness.

Is this query really so slow? Anything I can do to improve it?

Additional information:

The query 

explain select * from Table1 AS m 
LEFT JOIN 
Table2 AS i 
ON m.id = i.id 
WHERE m.state='in'

yields the following output:

+----+-------------+-------+------+-----------------------------------------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys                                 | key       | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------------------------------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | m     | ref  | state,state_2,state_3,state_4 | state | 1       | const |    1 | Using where | 
|  1 | SIMPLE      | i     | ALL  | id,id_2,id_3                            | NULL      | NULL    | NULL  |    2 |             | 
+----+-------------+-------+------+-----------------------------------------------+-----------+---------+-------+------+-------------+
Alex
  • 41,580
  • 88
  • 260
  • 469
  • 1
    The first index is technically not necessary; perhaps it would help to share with us the output of `EXPLAIN ;`. And what's the engine you're using? – Ja͢ck Apr 02 '14 at 05:49
  • What exactly do you mean by `EXPLAIN ;`? Using it literally gives me a `SQL syntax error`. I do not know `mysql` very good... – Alex Apr 02 '14 at 05:52
  • Check [here](https://dev.mysql.com/doc/refman/5.0/en/explain.html) on how to use `EXPLAIN`. – Ja͢ck Apr 02 '14 at 05:53
  • So I guess I used it correctly. Except it only can be used on a select statement and not on delete. In any way, I am unable to provide you with the information you would like to see. – Alex Apr 02 '14 at 05:55
  • The first thing I would do, judging from the `EXPLAIN` output, is to remove all those other indexes i.e. `state_2`, `state_3`, `state_4`, `id_2` and `id_3`. – Ja͢ck Apr 02 '14 at 06:15
  • Again, as a `mysql` novice, I do not know how to 'remove' those indices. They do not show up if I do a `select * from Table1`. – Alex Apr 02 '14 at 06:19
  • Check the manual on [`DROP INDEX`](https://dev.mysql.com/doc/refman/5.0/en/drop-index.html) – Ja͢ck Apr 02 '14 at 06:24
  • I can't really help you if you don't bother to read the manual =/ – Ja͢ck Apr 02 '14 at 06:32
  • 1
    Note that, except for exclusion joins, outer joins on a query such as this are vanishingly rare and faintly nonsensical. – Strawberry Apr 02 '14 at 06:32
  • The name of the table is `Table1`, not `m`. You want `drop index state_2 on Table1`. – Alp Apr 02 '14 at 06:34

2 Answers2

0

I have tried to replicate what you are doing my code is below

delete t1, t2 FROM sandbox.t1 t1
left join sandbox.t2 t2 on
    t2.id = t1.id
where
    t1.state = 'a';

This runs in less than a second. I would recommend removing any indexes since you hardly have any data (not that it should make a difference) and just to strip down the problem. If you are using mysql workbench you will need to right click on your table and alter it. otherwise google drop index to find how to drop indexes.

The only thing I can think that would be slowing this down is something is locking your table and your query is waiting for that to finish. You would need to kill that process which you can also do via mysql workbench. you could also restart your mysql ... again I don't know your setup so it's a bit hard to tell you how to go about this.

Ryan-Neal Mes
  • 6,003
  • 7
  • 52
  • 77
0

Delete's are going to be slower than the equivalent select statement, because the delete is doing much more work. In particular, each row is being logged as it is deleted. Then any indexes are being modified.

I would start by looking at the performance of the basic query:

select *
from Table1 AS m LEFT JOIN 
     Table2 AS i 
     ON m.id = i.id 
WHERE m.state = 'in';

The optimal indexes for this are Table1(state, id) and Table(id). I would expect the delete to then run about an order of magnitude slower than the select.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for your suggestion, but it does not bring any performance increase. Maybe the performance cannot be increased at all without defining a different table layout? – Alex Apr 02 '14 at 06:47
  • @Alex . . . Your expectation of the performance for the `delete` might be the problem. How many rows are you deleting in the two tables? – Gordon Linoff Apr 02 '14 at 06:55
  • About 400 rows in 2.2 seconds. Selecting 1100 rows from a table only takes 0.10 seconds otherwise. That's a factor 60 slower... – Alex Apr 02 '14 at 08:29
  • @Alex . . . Look at the accepted answer here: http://stackoverflow.com/questions/812512/faster-way-to-delete-matching-rows. – Gordon Linoff Apr 02 '14 at 08:37