On production servers I need sometime to delete a few 100,000s of rows . The query is optimized but deleting at once will cause servers freeze so I do it manually in chunks deleting for example 5000 rows each time. To allow the server to clear pending queries and to make the table locks less obvious to end users. I use MyISAM engine.
Isnt there a tool /command/ method that does long queries like this in low precedence or in chunks or in a way that doesnt get the server stuck in cases where its OK if it will take a long time to complete?
whats the best practices?