Re: Really Fixed?

[Mikael Ronström]

Hi,

marc steikert wrote:
> I thought about Fixed Tables that "Each row is
> stored using a fixed number of bytes" but ...
> mysql> create table t (id int) engine = myisam
> partition by hash(id) partitions 2;
> Query OK, 0 rows affected (0.04 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-04-30 00:13:23
> Update_time: 2006-04-30 00:13:23
> Check_time: NULL
> Collation: utf8_general_ci
> Checksum: NULL
> Create_options: partitioned
> Comment:
> 1 row in set (0.00 sec)
>

So 9 entries are allocated in file with 7 bytes per record

> 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: 0
> Auto_increment: NULL
> Create_time: 2006-04-30 00:13:23
> Update_time: 2006-04-30 00:13:23
> Check_time: NULL
> Collation: utf8_general_ci
> Checksum: NULL
> Create_options: partitioned
> Comment:
> 1 row in set (0.00 sec)
>

So the delete removes 4 entries, these entries do however still remain in
the MyISAM data file, thus the file remains 63 byte long.

> mysql> alter table t optimize partition p0,p1;
> Query OK, 0 rows affected (0.01 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-04-30 00:13:23
> Update_time: 2006-04-30 00:13:23
> Check_time: 2006-04-30 00:13:23
> Collation: utf8_general_ci
> Checksum: NULL
> Create_options: partitioned
> Comment:
> 1 row in set (0.00 sec)
>

So after optimize partitions there is only 5 entries left of 7 bytes each.

>
> ... it look's like dynamic
> ... and thus is Data_free = 0 ???

After checking things I noticed that you spotted a bug. The variable
delete_length which is used to calculate Data_free wasn't handled in the
::info call in the partition handler. I'll file a bug and fix it.