On Tue, 21 May 2013 19:45:02 +0300, Michael Widenius <monty@askmonty.org> wrote :
This is an optimization that Heikki did a long time ago that has this strange side effect.
See the comment posted by Radek on http://dev.mysql.com/doc/refman/5.5/en/delete.html
I found that a bit after posting, yes... Is there any way to undo that optimisation (configuration...) ? What kind of problems happened before that optimisation ? On a loosely related topic: I've witnessed innodb tables with a lot of insert & delete ("0 rows, then 300k rows, then back to 0 rows" kind of life cycle, table being used as a task queue) that queries can become slow even on an empty table. In such event, "explain select * from foo" returns a large number of rows to scan, although count(*) returns 0. I suspect there is a GC-ish bug in innodb which keeps deleted records for too long (as I believe rows are not actually freed on the spot, but kept for a while, maybe while a snapshot keeps referencing them). The cure is usually to restart mariadb, even closing all client connections doesn't solve this issue. Any chance it could be linked to such "bending the isolation level" ? Regards, -- Vincent Pelletier ERP5 - open source ERP/CRM for flexible enterprises