> 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.