0

I am trying to delete many rows using primary key search.

e.g. 

DELETE FROM t1 WHERE t1.pid IN (SELECT pid FROM ...);

Here pid is primary key of table t1 but it is not using index in case of delete.
The inner query is returning too many rows so the whole query is taking too much time as outer one is not using index.

How can I delete those rows faster?

Marco
  • 56,740
  • 14
  • 129
  • 152
Tejas Joshi
  • 197
  • 3
  • 12
  • "but it is not using index in case of delete" how are you sure about this? How are you checking? – Adrian Nasui Dec 17 '14 at 08:50
  • If you try to delete the major part of the table, there's no performance advantage in using the index. – jarlh Dec 17 '14 at 08:52
  • possible duplicate of [MySQL delete statement optimization](http://stackoverflow.com/questions/1987375/mysql-delete-statement-optimization) – Adrian Nasui Dec 17 '14 at 08:55
  • @AdrianNasui .. I have checked with explain statement... – Tejas Joshi Dec 17 '14 at 09:33
  • 1
    The MySQL optimizer is *really* bad with sub-selects, especially when it comes to `DELETE` statements. Use a join as shown by Marco –  Dec 17 '14 at 10:43

1 Answers1

0

You should avoid the subquery, but use JOIN instead:

DELETE t1
FROM t1 INNER JOIN t2 ON t1.pid = t2.pid
[WHERE .....]

Naturally t2 is the table you're getting pid from in your subquery; consider you can also add the WHERE clause to limit selected (and so deleted) pids...
Also consider that pid column on t2 table should be indexed to speed up query...

Marco
  • 56,740
  • 14
  • 129
  • 152