MySQL Performance – Size of MyISAM Table Doubles After OPTIMIZE

MySQLperformancesize;

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:

enter image description here

Then I delete 2'454 rows [< 0.6%], which produces an overhead of around 5MB which is also documented in PMA:

enter image description here

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!

enter image description here

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.

enter image description here

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

Best Answer

It's a 4 month ago filed bug and it is fixed in MySQL 8.0.22.

Related Question