4.4.8 Getting Information About a Table

To get 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 using the --skip-external-locking option, myisamchk may report an error for a table that is updated while it runs. However, because myisamchk doesn't change the table in describe mode, there isn't any risk of destroying data.
• myisamchk -d -v tbl_name To produce more information about what myisamchk is doing, add -v to tell it to run in verbose mode.
• myisamchk -eis tbl_name Shows only the most important information from a table. It is slow because it must read the whole table.
• myisamchk -eiv tbl_name This is like -eis, but tells you what is being done.

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
Datafilepointer (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

Here are the sizes of the data and index files for the table used in the preceding examples:

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

Explanations for the types of information myisamchk produces are given here. The ``keyfile'' is the index file. ``Record'' and ``row'' are synonymous:

• ISAM file Name of the ISAM (index) file.
• Isam-version Version of ISAM format. Currently always 2.
• Creation time When the datafile was created.
• Recover time When the index/datafile was last reconstructed.
• Data records How many records are in the table.
• Deleted blocks How many deleted blocks still have reserved space. You can optimise your table to minimise this space. See section 4.4.6.10 Table Optimisation.
• Data file: Parts For dynamic record format, this indicates how many data blocks there are. For an optimised table without fragmented records, this is the same as Data records.
• Deleted data How many bytes of non-reclaimed deleted data there are. You can optimise your table to minimise this space. See section 4.4.6.10 Table Optimisation.
• Data file pointer The size of the datafile 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 record 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's datafile (`.MYD' file) can become, in bytes.
• Max keyfile length How long the table's key file (`.MYI' file) can become, in bytes.
• Recordlength How much space each record takes, in bytes.
• Record format The format used to store table rows. The examples shown above use Fixed length. Other possible values are Compressed and Packed.
• 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 this index part starts.
  • Len How long this index part 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 unique or multip. (multiple). Indicates whether one value can exist multiple times in this index.
  • Type What data-type this index part has. This is an ISAM data-type with the options 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.
  • 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 myisamchk -a. If this is not updated at all, a default value of 30 is given.
• In the first example above, the 9th key is a multi-part key with two parts.
• Keyblocks used What percentage of the keyblocks are used. Because the table used in the examples had just been reorganised with myisamchk, the values are very high (very near the 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 third example above, the 4th 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 keys get high values.
• Records How many rows are in the table.
• M.recordlength The average record length. For tables with fixed-length records, this is the exact record 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 datafile is used.
• Empty space What percentage of the datafile is unused.
• Blocks/Record Average number of blocks per record (that is, how many links a fragmented record 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 big, you can reorganise the table with myisamchk. See section 4.4.6.10 Table Optimisation.
• Recordblocks How many blocks (links) are used. For fixed format, this is the same as the number of records.
• Deleteblocks How many blocks (links) are deleted.
• Recorddata How many bytes in the datafile are used.
• Deleted data How many bytes in the datafile are deleted (unused).
• Lost space If a record 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, record 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.7.4 myisampack, The MySQL Compressed Read-only Table Generator, for an example of this information and a description of what it means.

User Comments

Add your own comment.