4.6.4 Getting Information About a Table - MySQL

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

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

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 records 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 4.6.2.10, "Table Optimization."

• Datafile parts

  For dynamic record format, this indicates how many data blocks there are. For an optimized table without fragmented records, 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 4.6.2.10, "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 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 data file can become, in bytes.

• Max keyfile length

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

• Recordlength

  How much space each record 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 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

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

• Type

  What data type this index part has. This is a MyISAM 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 when MySQL is built from source.

• Rec/key

  This is a statistical value used by the optimizer. 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.

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 the theoretical maximum).

• Packed

  MySQL tries to pack keys with a common suffix. This can only be used for indexes on CHAR, VARCHAR, or DECIMAL columns. For long indexed strings that have similar leftmost parts, this can significantly reduce the space used. In the third example above, 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 record length. This is the exact record length for tables with fixed-length records, because all records 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 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 reorganize the table.

• 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 data file are used.

• Deleted data

  How many bytes in the data file 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 7.2, "myisampack, the MySQL Compressed Read-Only Table Generator," for an example of this information and a description of what it means.

Please check back next week for the continuation of this article.