Am 03.10.2013, 08:59 Uhr, schrieb Thomas Raschbacher <[email protected]>:
On 2013-10-02 12:50, Paul J Stevens wrote:
The maintenance runs are already designed to avoid long-running table
locks.
I would prefer something like:
BEGIN;
DELETE FROM table WHERE table.id in (SELECT ...);
COMMIT;
but this will lock up the table involved for the duration, which could
be long. Also, mysql (still) being somewhat brain-dead it does allow you
to reference the table you delete from in the sub-select.
So, as a compromise dbmail first fetches a list of ids to delete, and
then iterates over the list, deleting the rows involved one by one.
This is the slowest possible solution, but also one that doesn't hold
long-lasting write locks.
As to
very short stored procedure (which should be easy to maintain on all
the platforms)
that seems to me a contradiction. Each database platform seems to have
it's own ideas about syntax and capabilities of stored procedures.
But indeed, they would be very fast.
Apart from the mysql issue you mentioned.
Would it not be a good compromise to do something like this instead of
every row seperately?:
BEGIN;
DELETE FROM table WHERE table.id in (SELECT ... LIMIT 100);
COMMIT;
that way it is not that many queries and also doesn'T put long write
locks - the 100 is just an example but I assume you get what I mean ;)
not sure how you'd go about the mysql problem though without a stored
procedure, or temporary table / view maybe?
for mysql (any maybe other db engines) you could use:
DELETE FROM table where id in (SELECT id FROM (SELECT id FROM table) AS
tmptable);
stacking 2 select tables forces mysql to use a temp table, thie could also
have limit clause
Alternative I would do it in dbmail by giving a list of ids using WHERE id
in (1,2,3).
Regards
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
--
Harald Leithner
ITronic
Wiedner Hauptstraße 120-124/5.1, 1050 Wien, Austria
Tel: +43-1-545 0 604
Fax: +43-1-786 23 88 26
Mobil: +43-699-123 78 4 78
Mail: [email protected] | itronic.at
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
