MySQL Bugs: #99797: Size of MyISAM table doubles after OPTIMIZE

Bug #99797	Size of MyISAM table doubles after OPTIMIZE
Submitted:	7 Jun 2020 18:12	Modified:	8 Jun 2020 6:06
Reporter:	Peter VARGA	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S5 (Performance)
Version:	8.0.20	OS:	SUSE (15.1)
Assigned to:		CPU Architecture:	x86

Description:
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 whic is also confirmed by 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!

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.

How to repeat:
1) Delete several thousand of rows
2) Run OPTIMIZE table

Suggested fix:
The table should become smaller by the stated overhead instead of double in size.

Looks like duplicate of :
https://bugs.mysql.com/bug.php?id=98511

Hello Peter VARGA,

Thank you for the report and feedback.
As my senior Shane pointed out, this is duplicate of Bug #98511, please see Bug #98511.  Even if you feel that your issue is somewhat different, the resolution is likely to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

regards,
Umesh