Re: Insert Into ... Select, Delte Data
Posted by:
Rick James
Date: September 09, 2014 11:31AM
"Don't queue it, just do it." That is one of my mantras for efficient use of MySQL. You may be spending more time enqueuing/dequeuing that you are actually spending in doing the tasks.
A MySQL queue becomes especially costly if you set up Replication to make it even more robust.
You have done one good thing -- you have a fallback (outside of MySQL) in case something fails.
> execute the big Insert statements but without much success.
Eh? What went wrong? This is what I am talking about:
INSERT INTO queue (a,b,c) VALUES (1,2,3), (4,5,6), ...;
It does take some work to building the "(1,2,3)" strings, especially when there are strings that might need escaping.
> I tought about changing to InnoDb but this will cost some productivity time of the application. (Copying DB and changing engine).
Just ALTER TABLE queue ENGINE=InnoDB. However, if there is not a PRIMARY KEY, you should do that in the same ALTER.
> If the sql server chrashes thats not a big deal because the tasks are undone.
Plus the time to do REPAIR TABLE queue;