drop table if exists t; create table t ( id int, name varchar(255), description varchar(255), count int, primary key(id)) engine=myisam; set @a:=0; insert into t values(@a:=@a+1,concat('test',@a),concat('description',@a),@a); insert into t values(@a:=@a+1,concat('test',@a),concat('description',@a),@a); insert into t values(@a:=@a+1,concat('test',@a),concat('description',@a),@a); insert into t values(@a:=@a+1,concat('test',@a),concat('description',@a),@a); insert into t select @a:=@a+1,concat('test',@a),concat('description',@a),@a from t,t a,t b,t c,t d,t e,t f,t g,t h,t i; flush tables; show table status like 't'\G select version(); ====== dir C:\ade\mysql-commercial-8.0.19-winx64\data\test C:\ade\mysql-commercial-8.0.19-winx64\bin\myisamchk -ei C:\ade\mysql-commercial-8.0.19-winx64\data\test\t ====== update t set name=concat('testing test',id) where id%2=0; flush tables; show table status like 't'\G flush tables; ====== dir C:\ade\mysql-commercial-8.0.19-winx64\data\test C:\ade\mysql-commercial-8.0.19-winx64\bin\myisamchk -ei C:\ade\mysql-commercial-8.0.19-winx64\data\test\t ====== optimize table t; flush tables; ====== dir C:\ade\mysql-commercial-8.0.19-winx64\data\test C:\ade\mysql-commercial-8.0.19-winx64\bin\myisamchk -ei C:\ade\mysql-commercial-8.0.19-winx64\data\test\t ====== <<<<<<<<<<>>>>>>>>>>>>>>>>>>> OUTPUT <<<<<<<<<<>>>>>>>>>>>>>>>>>>> mysql> drop table if exists t; Query OK, 0 rows affected (0.04 sec) mysql> create table t ( id int, name varchar(255), description varchar(255), count int, primary key(id)) engine=myisam; Query OK, 0 rows affected (0.01 sec) mysql> set @a:=0; Query OK, 0 rows affected (0.00 sec) mysql> insert into t values(@a:=@a+1,concat('test',@a),concat('description',@a),@a); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into t values(@a:=@a+1,concat('test',@a),concat('description',@a),@a); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into t values(@a:=@a+1,concat('test',@a),concat('description',@a),@a); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into t values(@a:=@a+1,concat('test',@a),concat('description',@a),@a); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into t select @a:=@a+1,concat('test',@a),concat('description',@a),@a -> from t,t a,t b,t c,t d,t e,t f,t g,t h,t i; Query OK, 1048576 rows affected, 1 warning (29.16 sec) Records: 1048576 Duplicates: 0 Warnings: 1 mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> show table status like 't'\G *************************** 1. row *************************** Name: t Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 1048580 Avg_row_length: 43 Data_length: 45733492 Max_data_length: 281474976710655 Index_length: 10779648 Data_free: 0 Auto_increment: 1 Create_time: 2020-02-07 13:14:42 Update_time: 2020-02-07 13:15:11 Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.01 sec) mysql> select version(); +-------------------+ | version() | +-------------------+ | 8.0.19-commercial | +-------------------+ 1 row in set (0.00 sec) ================ C:\ade\mysql-commercial-8.0.19-winx64\data\test>dir C:\ade\mysql-commercial-8.0.19-winx64\data\test Volume in drive C is System Volume Serial Number is 543B-B8D3 Directory of C:\ade\mysql-commercial-8.0.19-winx64\data\test 2020/02/07 01:14 PM . 2020/02/07 01:14 PM .. 2020/02/07 01:15 PM 45 733 492 t.MYD 2020/02/07 01:15 PM 10 779 648 t.MYI 2020/02/07 01:14 PM 4 005 t_350.sdi 3 File(s) 56 517 145 bytes 2 Dir(s) 79 206 113 280 bytes free C:\ade\mysql-commercial-8.0.19-winx64\data\test>C:\ade\mysql-commercial-8.0.19-winx64\bin\myisamchk -ei C:\ade\mysql-commercial-8.0.19-winx64\data\test\t Checking MyISAM file: C:\ade\mysql-commercial-8.0.19-winx64\data\test\t Data records: 1048580 Deleted blocks: 0 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 98% Packed: 0% Max levels: 4 Total: Keyblocks used: 98% Packed: 0% - check records and index references Records: 1048580 M.recordlength: 38 Packed: 98% Recordspace used: 98% Empty space: 2% Blocks/Record: 1.00 Record blocks: 1048580 Delete blocks: 0 Record data: 40769572 Deleted data: 0 Lost space: 909090 Linkdata: 4054830 ================== mysql> mysql> update t set name=concat('testing test',id) where id%2=0; Query OK, 524290 rows affected (1 min 5.10 sec) Rows matched: 524290 Changed: 524290 Warnings: 0 mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> show table status like 't'\G *************************** 1. row *************************** Name: t Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 1048580 Avg_row_length: 43 Data_length: 45733492 Max_data_length: 281474976710655 Index_length: 10779648 Data_free: 0 Auto_increment: 1 Create_time: 2020-02-07 13:14:42 Update_time: 2020-02-07 13:15:11 Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.01 sec) mysql> flush tables; Query OK, 0 rows affected (0.00 sec) =============== C:\ade\mysql-commercial-8.0.19-winx64\data\test>dir C:\ade\mysql-commercial-8.0.19-winx64\data\test Volume in drive C is System Volume Serial Number is 543B-B8D3 Directory of C:\ade\mysql-commercial-8.0.19-winx64\data\test 2020/02/07 01:14 PM . 2020/02/07 01:14 PM .. 2020/02/07 01:16 PM 56 498 272 t.MYD 2020/02/07 01:16 PM 10 779 648 t.MYI 2020/02/07 01:14 PM 4 005 t_350.sdi 3 File(s) 67 281 925 bytes 2 Dir(s) 79 148 707 840 bytes free C:\ade\mysql-commercial-8.0.19-winx64\data\test>C:\ade\mysql-commercial-8.0.19-winx64\bin\myisamchk -ei C:\ade\mysql-commercial-8.0.19-winx64\data\test\t Checking MyISAM file: C:\ade\mysql-commercial-8.0.19-winx64\data\test\t Data records: 1048580 Deleted blocks: 0 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 98% Packed: 0% Max levels: 4 Total: Keyblocks used: 98% Packed: 0% - check records and index references Records: 1048580 M.recordlength: 42 Packed: 97% Recordspace used: 99% Empty space: 1% Blocks/Record: 1.50 Record blocks: 1572870 Delete blocks: 0 Record data: 44963892 Deleted data: 0 Lost space: 594035 Linkdata: 10940345 ================= mysql> optimize table t; +--------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------+----------+----------+----------+ | test.t | optimize | status | OK | +--------+----------+----------+----------+ 1 row in set (8.76 sec) mysql> flush tables; Query OK, 0 rows affected (0.00 sec) ================= C:\ade\mysql-commercial-8.0.19-winx64\data\test>dir C:\ade\mysql-commercial-8.0.19-winx64\data\test Volume in drive C is System Volume Serial Number is 543B-B8D3 Directory of C:\ade\mysql-commercial-8.0.19-winx64\data\test 2020/02/07 01:17 PM . 2020/02/07 01:17 PM .. 2020/02/07 01:17 PM 94 352 220 t.MYD <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2020/02/07 01:17 PM 10 700 800 t.MYI 2020/02/07 01:14 PM 4 005 t_350.sdi 3 File(s) 105 057 025 bytes 2 Dir(s) 79 110 668 288 bytes free C:\ade\mysql-commercial-8.0.19-winx64\data\test>C:\ade\mysql-commercial-8.0.19-winx64\bin\myisamchk -ei C:\ade\mysql-commercial-8.0.19-winx64\data\test\t Checking MyISAM file: C:\ade\mysql-commercial-8.0.19-winx64\data\test\t Data records: 1048580 Deleted blocks: 0 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 98% Packed: 0% Max levels: 4 Total: Keyblocks used: 98% Packed: 0% - check records and index references Records: 1048580 M.recordlength: 42 Packed: 96% Recordspace used: 94% Empty space: 2% Blocks/Record: 4.50 Record blocks: 4717611 Delete blocks: 0 Record data: 44963892 Deleted data: 0 Lost space: 2737957 Linkdata: 46650371 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<