How to Use Delete SQL in a Production Environment

I made a mistake in using the delete command in our production environment. I had 17 million rows and I only needed to keep 3 million rows, so I issued a delete sql to erase the 15 million rows. Bad move! Because I immediately saw how the I/O of the machine went up very high and the delete sql caused a huge load spike. On top of that, I had to run an optimize table command because MySQL did not free the deleted space and this caused another load spike.

In retrospect, I should have done the following items:

1. Instead of delete sql, I should have issued a create new table and select the 3 million rows from the old table. This would have been faster and it would not be necessary to run optimize table.

2. If I have to use delete and and optimize table, I could have copied the table to a non-production database and run the commands there. After it has finished executing all the processes, I can copy the new tables back to the production database. I have this option since there is no transaction happening on those tables on an hourly basis.