Skip navigation links

Additional languages
6.4.6 Setting Up a Table Maintenance Schedule »
Section Navigation      [Toggle]

6.4.5. Getting Information About a Table

To obtain a description of a table or statistics about it, use the commands shown here. We explain some of the information in more detail later.

  • myisamchk -d tbl_name

    Runs myisamchk in “describe mode” to produce a description of your table. If you start the MySQL server with external locking disabled, myisamchk may report an error for a table that is updated while it runs. However, because myisamchk does not change the table in describe mode, there is no risk of destroying data.

  • myisamchk -d -v tbl_name

    Adding -v runs myisamchk in verbose mode so that it produces more information about what it is doing.

  • myisamchk -eis tbl_name

    Shows only the most important information from a table. This operation is slow because it must read the entire table.

  • myisamchk -eiv tbl_name

    This is like -eis, but tells you what is being done.

The tbl_name argument can be either the name of a MyISAM table or the name of its index file, as described in Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”. Multiple tbl_name arguments can be given.

Sample output for some of these commands follows. They are based on a table with these data and index file sizes: 

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

Example of myisamchk -d output: 

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

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 myisamchk -d -v output: 

MyISAM file:         company
Record format:       Fixed length
File-version:        1
Creation time:       1999-10-30 12:12:51
Recover time:        1999-10-31 19:13:01
Status:              checked
Data records:            1403698  Deleted blocks:              0
Datafile parts:          1403698  Deleted data:                0
Datafile pointer (bytes):      3  Keyfile pointer (bytes):     3
Max datafile length:  3791650815  Max keyfile length: 4294967294
Recordlength:                226

table description:
Key Start Len Index   Type                  Rec/key     Root Blocksize
1   2     8   unique  double                      1 15845376      1024
2   15    10  multip. text packed stripped        2 25062400      1024
3   219   8   multip. double                     73 40907776      1024
4   63    10  multip. text packed stripped        5 48097280      1024
5   167   2   multip. unsigned short           4840 55200768      1024
6   177   4   multip. unsigned long            1346 65145856      1024
7   155   4   multip. text                     4995 75090944      1024
8   138   4   multip. unsigned long              87 85036032      1024
9   177   4   multip. unsigned long             178 96481280      1024
    193   1           text

Example of myisamchk -eis output: 

Checking MyISAM file: company
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
Record blocks:    1403698    Delete blocks:        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 context switches 639, Involuntary context switches 28966

Example of myisamchk -eiv output: 

Checking MyISAM file: company
Data records: 1403698   Deleted blocks:       0
- check file-size
- check delete-chain
block_size 1024:
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
Record blocks:   1403698   Delete blocks:      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 context switches 10604, Involuntary context switches 122798

Explanations for the types of information myisamchk produces are given here. “Keyfile” refers to the index file. “Record” and “row” are synonymous.

  • MyISAM file

    Name of the MyISAM (index) file.

  • File-version

    Version of MyISAM format. Currently always 2.

  • Creation time

    When the data file was created.

  • Recover time

    When the index/data file was last reconstructed.

  • Data records

    How many rows are in the table.

  • Deleted blocks

    How many deleted blocks still have reserved space. You can optimize your table to minimize this space. See Section 6.4.4, “Table Optimization”.

  • Datafile parts

    For dynamic-row format, this indicates how many data blocks there are. For an optimized table without fragmented rows, this is the same as Data records.

  • Deleted data

    How many bytes of unreclaimed deleted data there are. You can optimize your table to minimize this space. See Section 6.4.4, “Table Optimization”.

  • Datafile pointer

    The size of the data file pointer, in bytes. 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 row address. For dynamic tables, this is a byte address.

  • Keyfile pointer

    The size of the index file pointer, in bytes. 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 the table data file can become, in bytes.

  • Max keyfile length

    How long the table index file can become, in bytes.

  • Recordlength

    How much space each row takes, in bytes.

  • Record format

    The format used to store table rows. The preceding examples use Fixed length. Other possible values are Compressed and Packed.

  • table description

    A list of all keys in the table. For each key, myisamchk displays some low-level information:

    • Key

      This key's number.

    • Start

      Where in the row this portion of the index starts.

    • Len

      How long this portion of the index is. For packed numbers, this should always be the full length of the column. For strings, it may be shorter than the full length of the indexed column, because you can index a prefix of a string column.

    • Index

      Whether a key value can exist multiple times in the index. Possible values are unique or multip. (multiple).

    • Type

      What data type this portion of the index has. This is a MyISAM data type with the possible values packed, stripped, or empty.

    • Root

      Address of the root index block.

    • Blocksize

      The size of each index block. By default this is 1024, but the value may be changed at compile time when MySQL is built from source.

    • Rec/key

      This is a statistical value used by the optimizer. It tells how many rows there are per value for this index. A unique index always has a value of 1. This may be updated after a table is loaded (or greatly changed) with myisamchk -a. If this is not updated at all, a default value of 30 is given.

    For the table shown in the examples, there are two table description lines for the ninth index. This indicates that it is a multiple-part index with two parts.

  • Keyblocks used

    What percentage of the keyblocks are used. When a table has just been reorganized with myisamchk, as for the table in the examples, the values are very high (very near theoretical maximum).

  • Packed

    MySQL tries to pack key values that have a common suffix. This can only be used for indexes on CHAR and VARCHAR columns. For long indexed strings that have similar leftmost parts, this can significantly reduce the space used. In the third of the preceding examples, the fourth key is 10 characters long and a 60% reduction in space is achieved.

  • Max levels

    How deep the B-tree for this key is. Large tables with long key values get high values.

  • Records

    How many rows are in the table.

  • M.recordlength

    The average row length. This is the exact row length for tables with fixed-length rows, because all rows have the same length.

  • Packed

    MySQL strips spaces from the end of strings. The Packed value indicates the percentage of savings achieved by doing this.

  • Recordspace used

    What percentage of the data file is used.

  • Empty space

    What percentage of the data file is unused.

  • Blocks/Record

    Average number of blocks per row (that is, how many links a fragmented row is composed of). This is always 1.0 for fixed-format tables. This value should stay as close to 1.0 as possible. If it gets too large, you can reorganize the table. See Section 6.4.4, “Table Optimization”.

  • Recordblocks

    How many blocks (links) are used. For fixed-format tables, this is the same as the number of rows.

  • Deleteblocks

    How many blocks (links) are deleted.

  • Recorddata

    How many bytes in the data file are used.

  • Deleted data

    How many bytes in the data file are deleted (unused).

  • Lost space

    If a row is updated to a shorter length, some space is lost. This is the sum of all such losses, in bytes.

  • Linkdata

    When the dynamic table format is used, row fragments are linked with pointers (4 to 7 bytes each). Linkdata is the sum of the amount of storage used by all such pointers.

If a table has been compressed with myisampack, myisamchk -d prints additional information about each table column. See Section 4.6.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”, for an example of this information and a description of what it means.


User Comments

Posted by Are you mortal? Then prepare to die. on January 5 2004 4:23am[Delete] [Edit]

Blocksize The size of each index block. By default this is 1024, but the value may be changed at compile time.

What does this mean?

Posted by Matthias Fripp on October 24 2005 7:55am[Delete] [Edit]

The "keyfile pointer (bytes)" size can be important if your tables have many records (billions) and/or wide index keys. The keyfile pointer size directly determines the "Max keyfile length," which in turn determines how many records can be indexed. If the keyfile pointer size is too low, your .MYI file will reach its maximum size before all the records are indexed.

Surprisingly (to me) the "MAX_ROWS" parameter of the CREATE TABLE command affects the keyfile pointer size before it affects anything else. I made some tables without specifying MAX_ROWS, and MySQL happily filled them with 3-4 billion records. But then when I tried to index them, MySQL chose a 3-byte index file pointer, and ran out of index space after indexing only about 1.8 billion records. The solution was to issue the command ALTER TABLE myfile MAX_ROWS=4000000000. Then MySQL used a 4-byte keyfile pointer.

Note: In principle, MAX_ROWS should also change the datafile pointer size, but it didn't seem to have any effect for me (in 4.1.14-nt). So if you will have more than 2^32 (~4 billion) rows, you may need to set the myisam_data_pointer_size global variable before you create the table (see CREATE TABLE).

Add your own comment.