MySQL 8 Windows Deletion Performance Improvement?
I'm relatively new to MySQL but not to databases (40+ years). I see there are a number of topics about performance in this forum, and then I also noticed a not-very-clear note on switching to InnoDB.
First, I have a 119M record, three-field table that I will need to update once or twice a month. It's running on a Windows 2012 R2 server, MySQL 8.0, over 100GB hard drive space and 8 GB RAM (and more RAM is NOT in the budget at this time). There is not much else running on this server. I am doing my manual transactions using MySQL Workbench.
I'll first need to delete a number of rows based on their existence in another table, and then I'll need to insert a number of rows based on their non-existence in the existing table.
It's a MyISAM table and the two necessary fields are indexed.
The first time I tried to do the mass deletion, it ran for almost three days before I decided to kill it, thinking it was frozen. It was not, sorry to say.
So, for kicks, I tried another deletion with LIMIT 100. It's still running after an hour.
This is primarily a resource table; it won't otherwise be updated (e.g. record at a time updates based on user or other inputs).
How can I best speed this search up? Seems to me an hour - at the most - should get this done.