I have this table:
CREATE TABLE `foo` ( `CalculatedResultsId` int NOT NULL, `Md5Hash` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL, `SectionData` json NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; ALTER TABLE `foo` ADD UNIQUE KEY `CalculatedResultsId` (`CalculatedResultsId`), ADD UNIQUE KEY `Md5Hash` (`Md5Hash`);
which contains ~400k rows and is ~800MB big. This is the output from PMA:
Then I delete 2’454 rows [< 0.6%], which produces an overhead of around 5MB which is also documented in PMA:
OPTIMIZE Table suddenly doubles the size of this table. I checked the real size of the table file in the directory and this is correct. It really doubled!
Why, what is going on? What happened, what is the reason, that the table size doubled?
The only way I found how to reduce the size of the table as it was before is to create a new table and re-fill it. Another very strange fact:
Running this command in order to get the copy of the big table
CREATE TABLE foo_new AS SELECT * FROM foo; runs for 27! minutes for these 400k rows.
Creating an empty table and then copying with
INSERT INTO foo_new SELECT * FROM foo needs 24! seconds.
Why such a difference? I don’t know, but somehow it looks to me like a very serious design problem in MySQL, or a bug.
MySQL 8.0.20 running on SLES 15.1
It’s a 4 month ago filed bug and it is fixed in MySQL 8.0.22.
As for the slowness of the copying — For each row it must check that there is not already a dup in each
Two workarounds (maybe):
- Disable the indexes, do the load, then re-enable the indexes. (Hopefully, this will cause it to do a file sort, which might be faster.)
CREATEthe table without the indexes, then