Having a production database that is growing I started to make some tests (on a copy) in order to decide whit ROW_FORMAT should be better. The tables where created without specifying a format so no surprise when I found them stored in Dynamic format. Here is a line from show table status; command output:
+--------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+
| DOM_INTEGRATION | InnoDB | 10 | Dynamic | 33460 | 110 | 3686400 | 0 | 11059200 | 4194304 | NULL | 2022-01-10 13:27:26 | NULL | NULL | utf8_general_ci | NULL |
where 11059200 is the index length. Now simply typing:
mysql> ALTER TABLE DOM_INTEGRATION ROW_FORMAT=Dynamic;
Query OK, 0 rows affected (3.98 sec)
Records: 0 Duplicates: 0 Warnings: 0
Produce a sharp change in the index length:
| DOM_INTEGRATION | InnoDB | 10 | Dynamic | 32619 | 113 | 3686400 | 0 | 5816320 | 2097152 | NULL | 2022-01-10 14:37:05 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC | |
that is now reduced to 5816320 . What happened ? also the number of rows seems different? Setting a row format to Dynamic as it was I was expecting no change. This is the table structure and it's indices:
+------------+--------------+------+-----+---------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-----------------------------+
| DOMUPI | varchar(300) | NO | PRI | NULL | |
| POS | varchar(255) | NO | PRI | NULL | |
| PRODUPI | varchar(300) | YES | MUL | NULL | |
| B_id | int | YES | MUL | NULL | |
| Modifiable | tinyint(1) | NO | | 1 | |
| NPOS | int | YES | | NULL | VIRTUAL GENERATED |
| OpTime | timestamp | YES | | NULL | on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+---------+-----------------------------+
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| DOM_INTEGRATION | 0 | PRIMARY | 1 | DOMUPI | A | 649 | NULL | NULL | | BTREE | | | YES | NULL |
| DOM_INTEGRATION | 0 | PRIMARY | 2 | POS | A | 32619 | NULL | NULL | | BTREE | | | YES | NULL |
| DOM_INTEGRATION | 1 | POS | 1 | POS | A | 277 | NULL | NULL | | BTREE | | | YES | NULL |
| DOM_INTEGRATION | 1 | B_id | 1 | B_id | A | 192 | NULL | NULL | YES | BTREE | | | YES | NULL |
| DOM_INTEGRATION | 1 | IDX_DMP | 1 | PRODUPI | A | 18110 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
What I'm doing wrong?
