Re: Really Fixed?
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
Subject
Views
Written By
Posted
3105
April 29, 2006 04:54PM
2152
May 03, 2006 12:27AM
2159
May 22, 2006 01:25PM
2173
June 09, 2006 08:29AM
Re: Really Fixed?
2129
June 09, 2006 12:03PM
2174
June 09, 2006 03:48PM
2163
June 10, 2006 01:18AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.