Size of a MyISAM table doubles after OPTIMIZE

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:

Running 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!

My question:

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 UNIQUE index.

Two workarounds (maybe):

• Increase key_buffer_size.
• 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.)
• CREATE the table without the indexes, then ADD them.