Timeline for Why is DELETE so much slower than SELECT, then DELETE by id?
Current License: CC BY-SA 3.0
14 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jul 25, 2012 at 8:54 | vote | accept | itsadok | ||
| Jul 25, 2012 at 8:35 | history | edited | Aaron Brown | CC BY-SA 3.0 |
added 3983 characters in body
|
| Jul 25, 2012 at 8:35 | comment | added | Radu Murzea | @itsadok I wrote it as an answer :) . | |
| Jul 25, 2012 at 8:34 | answer | added | Radu Murzea | timeline score: 7 | |
| Jul 25, 2012 at 8:34 | comment | added | pconcepcion | @SoboLAN you are right, sorry... I don't know what I was thinking... | |
| Jul 25, 2012 at 8:34 | comment | added | Aaron Brown | Since it's a lock wait timeout, SHOW FULL PROCESSLIST and SHOW ENGINE INNODB STATUS are necessary. It's not that the DELETE is slow, it's that it conflicts with another open transaction that is slow. | |
| Jul 25, 2012 at 8:29 | comment | added | Radu Murzea |
@pconcepcion if you write EXPLAIN DELETE FROM...., it won't work. From what I know, it works only on SELECTs.
|
|
| Jul 25, 2012 at 8:27 | comment | added | pconcepcion |
Another explain of DELETE FROM ThreadNotification2 WHERE email='invalid address'; maybe would help also...
|
|
| Jul 25, 2012 at 7:35 | history | tweeted | twitter.com/#!/StackDBAs/status/228030824610672640 | ||
| Jul 25, 2012 at 7:22 | comment | added | Radu Murzea |
Yeah but.... you were right in the first place. If the field email is unindexed, then both DELETE and SELECT should work equally slow. Or: You say that the table is queried heavily. Maybe when you tried your first DELETE there was someone else running a really long transaction on those rows...
|
|
| Jul 25, 2012 at 7:19 | comment | added | itsadok | @SoboLAN really? It's seems like such a simple scenario. I updated the question. | |
| Jul 25, 2012 at 7:18 | history | edited | itsadok | CC BY-SA 3.0 |
added 428 characters in body
|
| Jul 25, 2012 at 6:22 | comment | added | Radu Murzea |
I guess you absolutely must post a SHOW CREATE TABLE and probably an EXPLAIN... too.
|
|
| Jul 25, 2012 at 5:52 | history | asked | itsadok | CC BY-SA 3.0 |