What's faster/more efficient: Continuous Deleting OR Continuous Updating + Intermittent Deleting?
Question by user371699
I have a cron that runs through many rows, deleting the “bad” ones (according to my criteria). I’m just wondering what would be the best to optimize the script. I can do one of the following:
-
Have the same cron instantly delete the “bad” rows upon finding them.
-
Have the same cron instantly update the “bad” rows to status “1”, meaning bad. Then, I can set up another cron that runs onces an hour deleting all rows with status “1”. To make it faster, I guess I’d have an index on “status”, but that might also ruin performance.
Any suggestions?
Answer by Juergen Hollfelder
I am not experienced in mySQL but on other DBMS I worked on an update and then delete does not help. Just try with huge amounts of data and measure the time on the delete versus update+delete. It helps if the columns that act as a criteria for the “bad” ones have an index.
Answer by Starx
IF you are thinking of updating a row as bad and then later deleting it, you are putting extra pressure on the server.
Deleting them directly is the better option.
If you think there are going to be huge amount of bad rows, make a cron in such a way that it does not delete more than 100 rows at a time. That should limit the server load to some extend.
