MySQL Forums
Forum List  »  Archive Storage Engine

Re: What does Avg_row_length mean on an ARCHIVE engine table?
Posted by: Roel Van de Paar
Date: April 01, 2009 06:12PM

> The value is the recordlength as needed for memory, plus the length of the largest blob seen.

This is not correct.

o The MEMORY engine does not support BLOB or TEXT:

-------------
mysql> CREATE TABLE test1 (testfield BLOB) ENGINE=MEMORY;
ERROR 1163 (42000): The used table type doesn't support BLOB/TEXT columns

"MEMORY tables cannot contain BLOB or TEXT columns."
http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html
-------------

o For a MEMORY TABLE, the 'Avg_row_length' value is the record length as needed for memory:

-------------
mysql> CREATE TABLE t8 (testfield char(200)) ENGINE=MEMORY;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t8 VALUES ("1234567890"),("1234567890"),("1234567890"),("1234
567890"),("1234567890"),("123456789012345678901234567890123456789012345678901234
5678901234567890123456789012345678901234567890");
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SHOW TABLE STATUS LIKE 't8'\G
*************************** 1. row ***************************
Name: t8
Engine: MEMORY
Version: 10
Row_format: Fixed
Rows: 6
Avg_row_length: 201
Data_length: 63648
-------------

And (notice varchar(200) instead of char(200)):

--------------
mysql> CREATE TABLE t4 (testfield varchar(200)) ENGINE=MEMORY;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t4 VALUES ("1234567890"),("1234567890"),("1234567890"),("1234
567890"),("1234567890"),("123456789012345678901234567890123456789012345678901234
5678901234567890123456789012345678901234567890");
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SHOW TABLE STATUS LIKE 't4'\G
*************************** 1. row ***************************
Name: t4
Engine: MEMORY
Version: 10
Row_format: Fixed
Rows: 6
Avg_row_length: 202
Data_length: 63648
--------------

The same goes for Fixed MyISAM tables.

For dynamic MyISAM tables, as well as for InnoDB tables, the Avg_row_length value is calculated as 'Data_length / Rows':

-------------
mysql> CREATE TABLE t7 (testfield blob) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t7 VALUES ("1234567890"),("1234567890"),("1234567890"),("1234
567890"),("1234567890"),("123456789012345678901234567890123456789012345678901234
5678901234567890123456789012345678901234567890");
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SHOW TABLE STATUS LIKE 't7'\G
*************************** 1. row ***************************
Name: t7
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 6
Avg_row_length: 34
Data_length: 208
-------------

And for InnoDB:

-------------
mysql> CREATE TABLE t6 (testfield blob) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t6 VALUES ("1234567890"),("1234567890"),("1234567890"),("1234
567890"),("1234567890"),("123456789012345678901234567890123456789012345678901234
5678901234567890123456789012345678901234567890");
Query OK, 6 rows affected (0.04 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SHOW TABLE STATUS LIKE 't6'\G
*************************** 1. row ***************************
Name: t6
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 6
Avg_row_length: 2730
Data_length: 16384
-------------

And (notice char(200) instead of blob):

-------------
mysql> CREATE TABLE t5 (testfield char(200)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t5 VALUES ("1234567890"),("1234567890"),("1234567890"),("1234
567890"),("1234567890"),("123456789012345678901234567890123456789012345678901234
5678901234567890123456789012345678901234567890");
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SHOW TABLE STATUS LIKE 't5'\G
*************************** 1. row ***************************
Name: t5
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 6
Avg_row_length: 2730
Data_length: 16384
-------------

Notice though that for InnoDB, this is also dependent on the physical row structure setting, as defined here:
http://dev.mysql.com/doc/refman/5.0/en/innodb-physical-record.html

Also, if you would like to figure out the actual average length of a field (or the average value), together with the optimal field type and some other information like the smallest and largest value, you can use PROCEDURE ANALYSE function:

--------------
mysql> CREATE TABLE t11 (testfield char(200)) ENGINE=MEMORY;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t11 VALUES ("1234"),("12345678");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM t11 PROCEDURE ANALYSE(10,256)\G
*************************** 1. row ***************************
Field_name: roelt.t11.testfield
Min_value: 1234
Max_value: 12345678
Min_length: 4
Max_length: 8
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 6.0000
Std: NULL
Optimal_fieldtype: ENUM('1234','12345678') NOT NULL
1 row in set (0.00 sec)
--------------

See http://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html for more information.



Edited 1 time(s). Last edit at 04/01/2009 06:18PM by Roel Van de Paar.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: What does Avg_row_length mean on an ARCHIVE engine table?
9339
April 01, 2009 06:12PM


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.