Re: Really Fixed?

[Mikael Ronström]

Hi Marc,
This is a property of MyISAM that deleted rows in a fixed size table are still
in the file, they are simply marked as deleted. Thus they are still part of the
calculation. However after an optimize or ALTER TABLE the table is freed from
"holes" and thus uses 7 bytes per record again.

Rgrds Mikael


marc steikert wrote:
> Hi Mikael,
> the Data_free length ist now correct but the
> Avg_row_length length is'nt.
> In Fixed Tables "Each row is stored using a fixed
> number of bytes" so must Avg_row_length length
> using 7 bytes per record (in this Example) or
> think i wrongly (see after delete)?
>
> mysql> select version();
> +-------------+
> | version() |
> +-------------+
> | 5.1.11-beta |
> +-------------+
> 1 row in set (0.00 sec)
>
>
> mysql> drop table if exists t;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> create table t (id int) engine = myisam
> partition by hash(id) partitions 2;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into t values
> (1),(2),(3),(4),(5),(6),(7),(8),(9);
> Query OK, 9 rows affected (0.00 sec)
> Datensätze: 9 Duplikate: 0 Warnungen: 0
>
> mysql> show table status like 't'\G
> *************************** 1. row
> ***************************
> Name: t
> Engine: MyISAM
> Version: 10
> Row_format: Fixed
> Rows: 9
> Avg_row_length: 7
> Data_length: 63
> Max_data_length: 0
> Index_length: 2048
> Data_free: 0
> Auto_increment: NULL
> Create_time: 2006-06-09 16:01:49
> Update_time: 2006-06-09 16:01:49
> Check_time: NULL
> Collation: utf8_general_ci
> Checksum: NULL
> Create_options: partitioned
> Comment:
> 1 row in set (0.00 sec)
>
> mysql> delete from t where id > 5;
> Query OK, 4 rows affected (0.00 sec)
>
> mysql> show table status like 't'\G
> *************************** 1. row
> ***************************
> Name: t
> Engine: MyISAM
> Version: 10
> Row_format: Fixed
> Rows: 5
> Avg_row_length: 12
> Data_length: 63
> Max_data_length: 0
> Index_length: 2048
> Data_free: 28
> Auto_increment: NULL
> Create_time: 2006-06-09 16:01:49
> Update_time: 2006-06-09 16:01:49
> Check_time: NULL
> Collation: utf8_general_ci
> Checksum: NULL
> Create_options: partitioned
> Comment:
> 1 row in set (0.00 sec)
>
> mysql> alter table t optimize partition p0,p1;
> Query OK, 0 rows affected (0.00 sec)
> Datensätze: 0 Duplikate: 0 Warnungen: 0
>
> mysql> show table status like 't'\G
> *************************** 1. row
> ***************************
> Name: t
> Engine: MyISAM
> Version: 10
> Row_format: Fixed
> Rows: 5
> Avg_row_length: 7
> Data_length: 35
> Max_data_length: 0
> Index_length: 2048
> Data_free: 0
> Auto_increment: NULL
> Create_time: 2006-06-09 16:01:49
> Update_time: 2006-06-09 16:01:50
> Check_time: 2006-06-09 16:01:50
> Collation: utf8_general_ci
> Checksum: NULL
> Create_options: partitioned
> Comment:
> 1 row in set (0.00 sec)
>
>
> Best Regards Marc