MySQL Forums
Forum List  »  MyISAM

MySQL 8 Windows Deletion Performance Improvement?
Posted by: Randy Smith
Date: January 14, 2020 03:24PM

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.

Options: ReplyQuote

Written By
MySQL 8 Windows Deletion Performance Improvement?
January 14, 2020 03:24PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.