On 10/02/2013 12:15 PM, Reindl Harald wrote: >> I ran a select query with count(*) (from that post) about 20 minutes ago... >> -- it is not done yet O.o
there is a reason dbmail always uses count(1) rather than count(*). > > because count(*) on InnoDB is terrible slow :-) > http://www.mysqltalk.org/select-count-select-countcolumn-in-innodb-vt144555.html > > the delete is much faster, i had these queries in my dail cronjob over the > last year > >> well guess i just have let it run until it is done. Although this might be >> good as some extra option like the >> migration with limit as well so that it doesn'T take forever ;) >> @paul what do you think about putting some limit on this? -- since if most >> people run a daily cronjob it would also >> get done eventually (or you could run it manually with a higher limit i >> suppose) 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. --- ________________________________________________________________ Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin * Premium Hosting Services and Web Application Consultancy * www.nfg.nl/i...@nfg.nl/+31.85.877.99.97 ________________________________________________________________ _______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
