MySQL Reference Manual for version .

Go to the first, previous, next, last section, table of contents.

Getting low level table information

To get a description/statistics from a table use the methods below. We will explain some of the information in more detail later.

isamchk -d table_name: isamchk in 'describe mode'. If one uses --skip-locking isamchk may report an error for a table that is updated while isamchk runs, but there isn't any risk of destroying data.
isamchk -d -v table_name: A little more verbose.
isamchk -eis table_name: Shows only the most important information from a table. It is slow since it must read the whole table.
isamchk -eiv table_name: Same as above but tells you what it is being done.

Example of isamchk -d output:

ISAM file:     company.ISM
Data records:           1403698  Deleted blocks:         0
Recordlength:               226
Record format: Fixed length

table description:
Key Start Len Index   Type
1   2     8   unique  double
2   15    10  multip. text packed stripped
3   219   8   multip. double
4   63    10  multip. text packed stripped
5   167   2   multip. unsigned short
6   177   4   multip. unsigned long
7   155   4   multip. text
8   138   4   multip. unsigned long
9   177   4   multip. unsigned long
    193   1           text

Example of isamchk -d -v output:

ISAM file:     company.ISM
Isam-version:  2
Creation time: 1996-08-28 11:44:22
Recover time:  1997-01-12 18:35:29
Data records:           1403698  Deleted blocks:              0
Datafile: Parts:        1403698  Deleted data:                0
Datafilepointer (bytes):      3  Keyfile pointer (bytes):     3
Max datafile length: 3791650815  Max keyfile length: 4294967294
Recordlength:               226
Record format: Fixed length
r
table description:
Key Start Len Index   Type                      Root Blocksize Rec/key
1   2     8   unique  double                15845376      1024       1
2   15    10  multip. text packed stripped  25062400      1024       2
3   219   8   multip. double                40907776      1024      73
4   63    10  multip. text packed stripped  48097280      1024       5
5   167   2   multip. unsigned short        55200768      1024    4840
6   177   4   multip. unsigned long         65145856      1024    1346
7   155   4   multip. text                  75090944      1024    4995
8   138   4   multip. unsigned long         85036032      1024      87
9   177   4   multip. unsigned long         96481280      1024     178
    193   1           text

Example of isamchk -eis output:

Checking ISAM file: company.ISM
Key:  1:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
Key:  2:  Keyblocks used:  98%  Packed:   50%  Max levels:  4
Key:  3:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
Key:  4:  Keyblocks used:  99%  Packed:   60%  Max levels:  3
Key:  5:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  6:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  7:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  8:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  9:  Keyblocks used:  98%  Packed:    0%  Max levels:  4
Total:    Keyblocks used:  98%  Packed:   17%

Records:          1403698    M.recordlength:     226   Packed:             0%
Recordspace used:     100%   Empty space:          0%  Blocks/Record:   1.00
Recordblocks:     1403698    Deleteblocks:         0
Recorddata:     317235748    Deleted data:         0
Lost space:             0    Linkdata:             0

User time 1626.51, System time 232.36
Maximum resident set size 0, Integral resident set size 0
Non physical pagefaults 0, Physical pagefaults 627, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary contexts switches 639, Involuntary contexts switches 28966

Example of isamchk -eiv output:

Checking ISAM file: company.ISM
Data records: 1403698   Deleted blocks:       0
- check file-size
- check delete-chain
index  1:
index  2:
index  3:
index  4:
index  5:
index  6:
index  7:
index  8:
index  9:
No recordlinks
- check index reference
- check data record references index: 1
Key:  1:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
- check data record references index: 2
Key:  2:  Keyblocks used:  98%  Packed:   50%  Max levels:  4
- check data record references index: 3
Key:  3:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
- check data record references index: 4
Key:  4:  Keyblocks used:  99%  Packed:   60%  Max levels:  3
- check data record references index: 5
Key:  5:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 6
Key:  6:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 7
Key:  7:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 8
Key:  8:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 9
Key:  9:  Keyblocks used:  98%  Packed:    0%  Max levels:  4
Total:    Keyblocks used:   9%  Packed:   17%

- check records and index references
[LOTS OF ROW NUMBERS DELETED]

Records:          1403698    M.recordlength:     226   Packed:             0%
Recordspace used:     100%   Empty space:          0%  Blocks/Record:   1.00
Recordblocks:     1403698    Deleteblocks:         0
Recorddata:     317235748    Deleted data:         0
Lost space:             0    Linkdata:             0

User time 1639.63, System time 251.61
Maximum resident set size 0, Integral resident set size 0
Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0
Blocks in 4 out 0, Messages in 0 out 0, Signals 0
Voluntary contexts switches 10604, Involuntary contexts switches 122798

Here are the data file sizes of the table used above.

-rw-rw-r--   1 monty    tcx     317235748 Jan 12 17:30 company.ISD
-rw-rw-r--   1 davida   tcx      96482304 Jan 12 18:35 company.ISM

Explanations for the things isamchk prints:

ISAM file: Name of isam file.
Isam-version: Version of isam format. Currently always 2.
Creation time: When was the data file created.
Recover time: When was the index/data file last reconstructed.
Data records: How many records/rows.
Deleted blocks: How many deleted blocks still have reserved space. See section How to repair tables..
Datafile: Parts: For dynamic record format this shows how many data blocks there are. For an optimised table without splits this is the same as Data records.
Deleted data: How many bytes of non-reclaimed deleted data.
Datafile pointer: Size (in bytes) of the datafile pointer. It is usually 2, 3, 4 or 5 bytes. Most tables manage with 2 bytes but this cannot be controlled from MySQL yet. For fixed tables this is a record address. For dynamic tables this is a byte address.
Keyfile pointer: How many bytes has the datafile pointer. It is usually 1, 2 or 3 bytes. Most tables manage with 2 bytes but this is calculated automatically by MySQL. It is always a block address.
Max datafile length: How long (in bytes) can the table's data file (.ISD) get.
Max keyfile length: How long (in bytes) can the table's key file (.ISM) get.
Recordlength: How much space does each record/row take.
Record format: Which format does each record/row have. This example uses Fixed length.
table description: A list of all keys in the table. For each key some low level information is presented.
Key: This key's number.
Start: Where in the record/row does this index-part start.
Len: How long is this index part. For packed numbers this should always be the full length of the column. For strings it may be shorter than the full length.
Index: unique or multip.. If one value can exist multiple times in this index.
Type: What data-type does this index part have. This is a C data-type with the options packed, stripped or empty.
Root: Address of the root index block.
Blocksize: The size of each index block. This is by default 1024 but may be changed at compile time.
Rec/key: This is a statistical value used by the optimiser. It tells how many records there are per value for this key. A unique key always has a value of 1. This may be updated after a table is loaded (or greatly changed) with isamchk -a. If this is not updated at all, a default value of 30 is given.
: The 9th key is a multi-part key with two parts.
Keyblocks used: What percentage of the keyblocks are used. Since this table has just been reorganised with isamchk the values are very high (very near theoretical maximum).
Packed: MySQL tries to pack keys with a common suffix. This can only be used for CHAR/VARCHAR/DECIMAL keys. For long strings like names, this can significantly reduce the space used. In the above example the 4th key is 10 characters long and gets a 60% reduction in space.
Max levels: How deep is the btree for this key. Large tables with long keys get high values.
Records: How many rows does the table have.
M.recordlength: Average recordlength. For fixed tables this is the recordlength.
Packed: MySQL strips spaces from the end of strings. What percentage did we save by doing this.
Recordspace used: What percentage of the datafile is used.
Empty space: What percentage of the datafile is unused.
Blocks/Record: How many blocks are there per record. This is always 1 for fixed format tables. This value should stay as close to 1.0 as possible. If it gets too big you can reorganise the table with isamchk. See section How to repair tables..
Recordblocks: How many blocks are used. For fixed format, this is the same as the number of records.
Deleteblocks: How many blocks are deleted.
Recorddata: How many bytes of actual user data there are in the datafile.
Deleted data: How many bytes of deleted data there are in the datafile.
Lost space: If a record is updated to a shorter length, some space is lost. This is the sum of all such losses.
Linkdata: When the dynamic format is used, blocks are linked with pointers (length 4-7 bytes). This is the sum of all such pointers.

Go to the first, previous, next, last section, table of contents.