Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Explore MyISAM clients and utilities for MariaDB Server. This section details specialized tools for managing and maintaining tables that utilize the MyISAM storage engine.
myisamchk's performance can be dramatically enhanced for larger tables by making sure that its memory-related variables are set to an optimum level.
By default, myisamchk will use very little memory (about 3MB is allocated), but can temporarily use a lot of disk space. If disk space is a limitation when repairing, the --safe-recover option should be used instead of --recover. However, if TMPDIR points to a memory file system, an out of memory error can easily be caused, as myisamchk places temporary files in TMPDIR. The --tmpdir=path option should be used in this case to specify a directory on disk.
myisamchk has the following requirements for disk space:
When repairing, space for twice the size of the data file, available in the same directory as the original file. This is for the original file as well as a copy. This space is not required if the --quick option is used, in which case only the index file is re-created.
Disk space in the temporary directory (TMPDIR or the tmpdir=path option) is needed for sorting if the --recover or --sort-recover options are used when not using --safe-recover). The space required is approximately (largest_key + row_pointer_length) * number_of_rows * 2. To get information about the length of the keys as well as the row pointer length, use myisamchk -dv table_name.
Space for a new index file to replace the existing one. The old index is first truncated, so unless the old index file is not present or is smaller for some reason, no significant extra space is needed.
There are a number of that are useful to adjust when running myisamchk. They will increase memory usage, and since some are per-session variables, you don't want to increase the general value, but you can either pass an increased value to myisamchk as a command line option, or with a [myisamchk] section in your file.
. By default this is 4M, but it's very useful to increase to make myisamchk sorting much faster. Since the server won't be running when you run myisamchk, you can increase substantially. 16M is usually a minimum, but values such as 256M are not uncommon if memory is available.
(which particularly helps with the --extend-check and --safe-recover options.
For example, if you have more than 512MB available to allocate to the process, the following settings could be used:
This page is licensed: CC BY-SA / Gnu FDL
myisampack is a tool for compressing tables. The resulting tables
are read-only, and usually about 40% to 70% smaller. It is run as follows:
The file_name is the .MYI index file. The extension can be omitted,
although keeping it permits wildcards, such as:
...to compress all the files.
myisampack
Once a table has been packed, use myisamchk -rq (the quick and recover options) to rebuild its indexes.
myisampack does not support partitioned tables or vector indexes.
Do not run myisampack if the tables could be updated during the operation, and skip_external_locking has been set.
The following variables can be set while passed as command line options tomyisampack, or set with a [myisampack] section in your my.cnf file.
-b, --backup
Make a backup of the table as table_name.OLD.
--character-sets-dir=name
Directory where character sets are.
-
## , --debug[=name]
Output debug log. Often this is 'd:t:o,filename'.
-f, --force
Force packing of table even if it gets bigger or if tempfile exists.
-j, --join=name
Join all given tables into 'new_table_name'. All tables must have identical layouts.
To uncompress a table compressed with myisampack, use the myisamchk -u option.
This page is licensed: CC BY-SA / Gnu FDL
myisampack [options] file_name [file_name2...]myisampack *.MYI> myisampack /var/lib/mysql/test/posts
Compressing /var/lib/mysql/test/posts.MYD: (1680 records)
- Calculating statistics
- Compressing file
37.71%
> myisamchk -rq /var/lib/mysql/test/posts
- check record delete-chain
- recovering (with sort) MyISAM-table '/var/lib/mysql/test/posts'
Data records: 1680
- Fixing index 1
- Fixing index 2myisamchk
--myisam_sort_buffer_size=256M
--key_buffer_size=512M
--read_buffer_size=64M
--write_buffer_size=64M
...-?, --help
Display help and exit.
-s, --silent
Only write output when an error occurs
-T, --tmpdir=name
Use temporary directory to store temporary table.
-t, --test
Don't pack table, only test packing it.
-v, --verbose
Write info about progress and packing result. Use multiple -v flags for more verbosity.
-V, --version
Output version information and exit.
-w, --wait
Wait and retry if table is in use.
myisamlog processes and returns the contents of a MyISAM log file.
Invoke myisamlog like this:
shell> myisamlog [options] [log_file [tbl_name] ...]
shell> isamlog [options] [log_file [tbl_name] ...]The default operation is update (-u). If a recovery is done (-r), all
writes and possibly updates and deletes are done and errors are only counted.
The default log file name is myisam.log for myisamlog and isam.log
for isamlog if no log_file argument is given. If tables are named on
the command line, only those tables are updated.
myisamlog supports the following options:
This page is licensed: CC BY-SA / Gnu FDL
myisam_ftdump is a utility for displaying information about indexes. It will scan and dump the entire index, and can be a lengthy process.
If the server is running, make sure you run a statement first.
The table_name can be specified with or without the .MYI index extension.
The index number refers to the number of the index when the table was defined, starting at zero. For example, take the following table definition:
The fulltext index is 2. The primary key is index
Navigating the complexities of database management requires the right tools and know-how, especially when dealing with specialized storage engines like MyISAM in MySQL and MariaDB environments. This comprehensive guide demystifies the role and functionality of MyISAM clients, offering you a deep dive into their essential features and operations. From initiating CRUD operations to optimizing table performance, we cover it all. We also spotlight dbForge Studio for MySQL, a feature-rich IDE that simplifies your interactions with MyISAM tables and databases. Whether you're a seasoned DBA or a developer looking to sharpen your database skills, this article serves as your go-to resource for understanding MyISAM clients and maximizing their capabilities.
A MyISAM client refers to a specialized application or module designed to interact with the MyISAM database storage engine. Its primary function is to facilitate essential database operations such as creating, reading, updating, and deleting data (commonly referred to as CRUD operations) through specific protocols, APIs, or query languages.
-r
Perform a recovery operation.
-R record_pos_file record_pos
Specify record position file and record position.
-u
Displays update operations.
-v
Verbose mode. Print more output about what the program does. This option can be given multiple times (-vv, -vvv) to produce more and more output.
-w write_file
Specify the write file.
-V
Display version information.
-?, -I
Display a help message and exit.
-c N
Execute only N commands.
-f N
Specify the maximum number of open files.
-i
Display extra information before exiting.
-o offset
Specify the starting offset.
-p N
Remove N components from path.
01You can use myisam_ftdump to generate a list of index entries in order of frequency of occurrence as follows:
-h, --help
Display help and exit.
-?, --help
Synonym for -h.
-c, --count
Calculate per-word stats (counts and global weights).
-d, --dump
Dump index (incl. data offsets and word weights).
-l, --length
Report length distribution.
-s, --stats
Report global stats.
This page is licensed: CC BY-SA / Gnu FDL
In simpler terms, any MyISAM client serves as the interface that allows users to interact with MySQL/MariaDB. There is a wide range of available solutions catering to different needs, including lightweight command line utilities like myisamchk, versatile options like the MySQL Command-Line Client, and feature-rich IDEs based on graphical user interfaces, such as MySQL Workbench or dbForge Studio for MySQL.
The choice of the right client largely depends on user preferences, the specific work environment, and the tasks they need to accomplish.
To better understand the functionality of a MyISAM client, let's delve into a practical scenario where a user needs to compose and execute a query against the database and subsequently retrieve the results. Whether you opt for a command line or GUI client, the process unfolds as follows:
Step 1: Connection Setup
The client initiates a connection to a MySQL/MariaDB server using the necessary library or connector. During this setup, it provides authentication credentials and specifies the database to be accessed.
Step 2: Query Submission
The client sends SQL queries to the MySQL/MariaDB server, utilizing the appropriate database engine—in our case, MyISAM.
Note: Modern GUI-based database clients, like dbForge Studio for MySQL and MariaDB, enhance query writing by offering essential features such as code auto-completion, formatting, debugging, code snippet libraries, and visual query builders for constructing intricate queries through diagrams.
Step 3: Query Execution
The database system takes charge of parsing and executing the SQL query. When the query involves a table managed by the MyISAM storage engine, MyISAM handles the data and its storage particulars.
Step 4: Result Retrieval
Upon completing query processing, the results are transmitted back to the client. The client then handles these results as needed—whether it's displaying them to the user or employing them for further operations.
Step 5: Connection Closure
Once the interaction is finished, the client responsibly closes the connection.
Throughout this process, it's essential to note that the client does not directly engage with MyISAM or any other storage engines. Instead, it interacts with the MySQL/MariaDB server, which, in turn, utilizes MyISAM to manage data at its level.
dbForge Studio for MySQL that is also a viable MariaDB tool offers full support for all MySQL storage engines, including MyISAM, and a user-friendly graphical interface that simplifies interactions with MyISAM tables. The functionality of the Studio makes it the common choice for professionals who prefer GUI tools over command line utilities.
It includes options available in the myisamchk utility and provides many more features to cover all database-related tasks on MySQL and MariaDB, no matter which storage engine those databases use.
Speaking of MyISAM tables, it is worth mentioning some basic operations.
In dbForge Studio for MySQL, you can check the storage engine of the current database in a couple of clicks. Select the database in Database Explorer, right-click the necessary table, and select Properties from the shortcut menu. The output will present the information of the database engine among other data.
If you need to switch between database engines for some tables, you can use the standard ALTER TABLE command. dbForge Studio for MySQL allows executing SQL queries directly against the database.
To convert an InnoDB table to MyISAM, use the following command:
ALTER TABLE database_name.table_name ENGINE=MyISAM;
Img 1 – InnoDB, img 2 – MyISAM
The same ALTER TABLE command serves in the opposite situation where you want to convert MyISAM tables to InnoDB. You will need to specify ENGINE=InnoDB in that case.
MyISAM does not support foreign key constraints that are supported by InnoDB. In case you want to convert an InnoDB table having foreign keys into MyISAM, you will encounter an error. Make sure to drop those constraints before converting. There are more differences between MyISAM and InnoDB that should be considered when you deal with these two storage engines.
The primary function of myisamchk is to check, optimize, and repair MyISAM tables. This is crucial because frequent CRUD operations (CREATE, READ, DELETE, UPDATE) can lead to table corruption and a gradual decline in performance over time. dbForge Studio for MySQL includes a table maintenance tool that simplifies the process of identifying, analyzing, optimizing, and repairing tables.
To locate tables that require the administrator's attention, you can run the standard command in dbForge Studio for MySQL:
SHOW TABLE STATUS LIKE table_name;
This command provides detailed information about the table properties, including the total space occupied by the table and the total unused space (Data_length and Data_free columns, respectively). This helps users pinpoint tables that may be consuming excessive space and may be corrupted.
This way, users can identify those tables that may take up too much space and be corrupted.
After detecting the MyISAM tables that require maintenance, you can apply the Studio’s integrated table maintenance tool to those tables to fix the issues. To launch it, right-click the necessary table in Database Explorer and select Table Maintenance.
The table maintenance module offers the following options:
Analyze the table: This tool examines the key statistics of the table, helping to create more efficient query plans for optimal performance.
Optimize the table: This option allows optimizing MyISAM tables for defragmentation, improving their performance.
Check table errors: With this option, you can run diagnostic tests on tables, identifying and reporting all errors and corruption. It's worth noting that dbForge Studio provides flexible configuration for error checking, allowing users to determine the level of detail required. While a thorough scan for all possible errors may take time, it ensures that every issue is detected and reports 100% consistency.
Checksum the table: This feature checks if the table has been modified, helping to maintain data integrity.
Repair the table: This command is used to fix corrupted MyISAM tables. Applying this option may also recommend upgrading tables if necessary to rectify errors.
dbForge Studio streamlines all of these tasks with a single click. Users only need to specify the desired option and click Execute. This simplifies the process of maintaining MyISAM tables in MySQL databases.
The functionality of myisamchk is covered by one of many features available in dbForge Studio for MySQL. This multi-featured IDE offers a comprehensive toolset with flexible configuration and robust customization capacities to perform all database-related tasks in MySQL and MariaDB, no matter which database storage engine is used there. The following aspects are just several examples of the Studio's work:
SQL Development: The functionality of this software client allows the users to construct and execute SQL queries and scripts, no matter the complexity, on databases using the MyISAM storage engine.
Database Design: Users can both build database structures from scratch and modify existing ones, including creating and managing MyISAM tables and their respective indexes and keys.
Database Administration: DBAs have access to essential tools for server connection management, performance monitoring, user account and privilege administration, and optimization and repair of tables using various storage engines.
Data Management: Beyond basic data export and import tasks, dbForge Studio allows users to browse and edit data directly in the grid. You can also sort and filter data according to your specific needs.
Backup and Restore: With support for MyISAM point-in-time backup and restore, the Studio can create backups of all types and restore them as needed. This ensures the safety, consistency, and stable performance of your databases.
Navigating the complexities of MyISAM storage engines doesn't have to be a daunting task. This article has provided you with an in-depth understanding of MyISAM clients and their integral role in MySQL and MariaDB database management. We've also showcased the unparalleled utility of dbForge Studio for MySQL, a one-stop IDE that simplifies everything from CRUD operations to advanced table maintenance. If you're keen on optimizing your database management processes, there's no better time to act.
Download a 30-day free trial of dbForge Studio to experience firsthand how this comprehensive tool can elevate your database operations to new heights.
This page is licensed: CC BY-SA / Gnu FDL
myisam_ftdump <table_name> <index_num>CREATE TABLE IF NOT EXISTS `employees_example` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(30) NOT NULL,
`last_name` VARCHAR(40) NOT NULL,
`position` VARCHAR(25) NOT NULL,
`home_address` VARCHAR(50) NOT NULL,
`home_phone` VARCHAR(12) NOT NULL,
`employee_code` VARCHAR(25) NOT NULL,
`bio` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `employee_code` (`employee_code`),
FULLTEXT (`bio`)
) ENGINE=MyISAM;myisam_ftdump -c mytexttable 1 | sort -r-v, --verbose
Be verbose.
myisamchk is a command line tool for checking, repairing and optimizing non-partitioned MyISAM tables.
myisamchk is run from the command line as follows:
The full list of options are listed below. One or more MyISAM tables can be specified. MyISAM tables have an associated .MYI index file, and the table name can either be specified with or without the .MYI extension. Referencing it with the extension allows you to use wildcards, so it's possible to run myisamchk on all the MyISAM tables in the database with *.MYI.
The path to the files must also be specified if they're not in the current directory.
myisamchk should not be run while anyone is accessing any of the affected tables. It is also best to make a backup before running.
With no options, myisamchk simply checks your table as the default operation.
The following options can be set while passed as command line options to myisamchk, or set with a [myisamchk] section in your file.
The following variables can also be set by using --var_name=value, for example --ft_min_word_len=5
If no option is provided, myisamchk will perform a check table. It is possible to check tables without shutting down or restricting access to the server by using instead.
The following check options are available:
It is also possible to repair tables by using .
The following repair options are available, and are applicable when using '-r' or '-o':
For more, see .
Check all the MyISAM tables in the current directory:
If you are not in the database directory, you can check all the tables there by specifying the path to the directory:
Check all tables in all databases by specifying a wildcard with the path to the MariaDB data directory:
The recommended way to quickly check all MyISAM tables:
Check all MyISAM tables and repair any that are corrupted:
This page is licensed: CC BY-SA / Gnu FDL
myisamchk [OPTIONS] tables[.MYI]-V, --version
Print version and exit.
-w, --wait
If table is locked, wait instead of returning an error.
--print-defaults
Print the program argument list and exit.
--no-defaults
Don't read default options from any option file.
--defaults-file=filename
Only read default options from the given file filename, which can be the full path, or the path relative to the current directory.
--defaults-extra-file=filename
Read the file filename, which can be the full path, or the path relative to the current directory, after the global files are read.
--defaults-group-suffix=str
Also read groups with a suffix of str. For example, --defaults-group-suffix=x would read the groups [myisamchk] and [myisamchk_x]
myisam_block_size
1024
134216704
myisam_sort_key_blocks
16
read_buffer_size
262136
sort_buffer_size
134216704
sort_key_blocks
16
stats_method
nulls_unequal
write_buffer_size
262136
-m, --medium-check
Faster than extend-check, but only finds 99.99% of all errors. Should be good enough for most cases.
-U --update-state
Mark tables as crashed if you find any errors. This should be used to get the full benefit of the --check-only-changed option, but you shouldn´t use this option if the mariadbd server is using the table and you are running it with external locking disabled.
-T, --read-only
Don't mark table as checked. This is useful if you use myisamchk to check a table that is in use by some other application that does not use locking, such as mariadbd when run with external locking disabled.
--create-missing-keys
Create missing keys. This assumes that the data file is correct and that the number of rows stored in the index file is correct. Enables --quick
--max-record-length=#
Skip rows larger than this if myisamchk can't allocate memory to hold them.
-r, --recover
Can fix almost anything except unique keys that aren't unique (a rare occurrence). Usually this is the best option to try first. Increase for better performance.
-n, --sort-recover
Forces recovering with sorting even if the temporary file would be very large.
-p, --parallel-recover
Uses the same technique as '-r' and '-n', but creates all the keys in parallel, in different threads.
-o, --safe-recover
Uses old recovery method; Slower than '-r' but uses less disk space and can handle a couple of cases where '-r' reports that it can't fix the data file. Increase key_buffer_size for better performance.
--character-sets-dir=directory_name
Directory where the are installed.
--set-collation=name
Change the collation (and by implication, the ) used by the index.
-q, --quick
Faster repair by not modifying the data file. One can give a second '-q' to force myisamchk to modify the original datafile in case of duplicate keys. NOTE: Tables where the data file is corrupted can't be fixed with this option.
-u, --unpack
Unpack file packed with myisampack.
-b offset, --block-search=offset
Find the record to which a block at the given offset belongs.
-H, --HELP
Display help and exit. Options are presented in a single list.
-?, --help
Display help and exit. Options are grouped by type of operation.
-# options, --debug=options
Write a debugging log. A typical debug options string is d:t:o,file_name. The default is d:t:o,/tmp/myisamchk.trace. (Available in debug builds only)
-t path, --tmpdir=path
Path for temporary files. Multiple paths can be specified, separated by colon (:) on Unix and semicolon (;) on Windows. They are used in a round-robin fashion. If not set, the TMPDIR environment variable is used.
-s, --silent
Only print errors. One can use two -s (-ss) to make myisamchk very silent.
-v, --verbose
Print more information. This can be used with --description and --check. Use many -v for more verbosity.
decode_bits
9
ft_max_word_len
version-dependent
ft_min_word_len
4
ft_stopword_file
built-in list
key_buffer_size
1044480
key_cache_block_size
1024
-c, --check
Check table for errors. This is the default operation if you specify no option that selects an operation type explicitly.
-e, --extend-check
Check the table VERY throughly. Only use this in extreme cases as it may be slow, and myisamchk should normally be able to find out if the table has errors even without this switch. Increasing the key_buffer_size can help speed the process up.
-F, --fast
Check only tables that haven't been closed properly.
-C, --check-only-changed
Check only tables that have changed since last check.
-f, --force
Restart with '-r' (recover) if there are any errors in the table. States is updated as with '--update-state'.
-i, --information
Print statistics information about the table that is checked.
-B, --backup
Make a backup of the .MYD file as 'filename-time.BAK'.
--correct-checksum
Correct the checksum information for table.
-D len, --data-file-length=#
Max length of data file (when recreating data file when it's full).
-e, --extend-check
Try to recover every possible row from the data file. Normally this will also find a lot of garbage rows; Don't use this option if you are not totally desperate.
-f, --force
Overwrite old temporary files. Add another --force to avoid 'myisam_sort_buffer_size is too small' errors. In this case we will attempt to do the repair with the given myisam_sort_buffer_size and dynamically allocate as many management buffers as needed.
-k val, --keys-used=#
Specify which keys to update. The value is a bit mask of which keys to use. Each binary bit corresponds to a table index, with the first index being bit 0. 0 disables all index updates, useful for faster inserts. Deactivated indexes can be reactivated by using myisamchk -r.
-a, --analyze
Analyze distribution of keys. Will make some joins faster as the join optimizer can better choose the order in which to join the tables and which indexes to use. You can check the calculated distribution by using '--description --verbose table_name' or SHOW INDEX FROM table_name.
--stats_method=name
Specifies how index statistics collection code should treat NULLs. Possible values of name are "nulls_unequal" (default), "nulls_equal" (emulate MySQL 4.0 behavior), and "nulls_ignored".
-d, --description
Print some descriptive information about the table. Specifying the --verbose option once or twice produces additional information.
-A [value], --set-auto-increment[=value]
Force auto_increment to start at this or higher value. If no value is given, then sets the next auto_increment value to the highest used value for the auto key + 1.
-S, --sort-index
Sort the index tree blocks in high-low order. This optimizes seeks and makes table scans that use indexes faster.
-R index_num, --sort-records=#
Sort records according to the given index (as specified by the index number). This makes your data much more localized and may speed up range-based SELECTs and ORDER BYs using this index. It may be VERY slow to do a sort the first time! To see the index numbers, SHOW INDEX displays table indexes in the same order that myisamchk sees them. The first index is 1.
can be used to obtain information about MyISAM tables, particularly with the -d, -e, -i and -v options.
Common options for gathering information include:
myisamchk -d
myisamchk -dv
myisamchk -dvv
myisamchk *.MYImyisamchk /path/to/database_dir/*.MYImyisamchk /path/to/datadir/*/*.MYImyisamchk --silent --fast /path/to/datadir/*/*.MYImyisamchk --silent --force --fast --update-state \
--key_buffer_size=64M --sort_buffer_size=64M \
--read_buffer_size=1M --write_buffer_size=1M \
/path/to/datadir/*/*.MYImyisamchk -ei
myisamchk -eiv
The -d option returns a short description of the table and its keys. Running the option while the table is being updated, and with external locking disabled, may result in an error, but no damage is done to the table. Each extra v adds more output. -e checks the table thoroughly (but slowly), and the -i options adds statistical information,
The following table describes the output from the running myisamchk with the -dvv option:
MyISAM file
Name and path of the MyISAM index file (without the extension)
Record format
. One of packed (dynamic), fixed or compressed.
Chararacter set
Default for the table.
File-version
Always 1.
Creation time
Time the data file was created
Recover time
Most recent time the file was reconstructed.
constant
All rows contain the same value.
no endspace
No endspace is stored.
no endspace, not_always
No endspace is stored, and endspace compression is not always performed for all values.
no endspace, no empty
No endspace is stored, no empty values are stored.
table-lookup
Column was converted to an .
zerofill(N)
Most significant N bytes of the value are not stored, as they are always zero.
The following table describes the output from the running myisamchk with the -eiv option:
Data records
Number of records in the table.
Deleted blocks
Number of deleted blocks that are still reserving space. Use to defragment.
Key
Index number, starting with one.
Keyblocks used
Percentage of the keyblocks that are used. Percentages is higher for optimized tables.
Packed
Percentage space saved from packing key values with a common suffix.
Max levels
Depth of the for the key. Larger tables and longer key values result in higher values.
This page is licensed: CC BY-SA / Gnu FDL
myisamchk -d /var/lib/mysql/test/posts
MyISAM file: /var/lib/mysql/test/posts
Record format: Compressed
Character set: utf8mb4_unicode_ci (224)
Data records: 1680 Deleted blocks: 0
Recordlength: 2758
Using only keys '0' of 5 possibly keys
table description:
Key Start Len Index Type
1 1 8 unique ulonglong
2 2265 80 multip. varchar prefix
63 80 varchar
17 5 binary
1 8 ulonglong
3 1231 8 multip. ulonglong
4 9 8 multip. ulonglong
5 387 764 multip. ? prefixmyisamchk -dvv /var/lib/mysql/test/posts
MyISAM file: /var/lib/mysql/test/posts
Record format: Compressed
Character set: utf8mb4_unicode_ci (224)
File-version: 1
Creation time: 2015-08-10 16:26:54
Recover time: 2015-08-10 16:26:54
Status: checked,analyzed,optimized keys
Auto increment key: 1 Last value: 1811
Checksum: 2299272165
Data records: 1680 Deleted blocks: 0
Datafile parts: 1680 Deleted data: 0
Datafile pointer (bytes): 6 Keyfile pointer (bytes): 6
Datafile length: 4298092 Keyfile length: 156672
Max datafile length: 281474976710654 Max keyfile length: 288230376151710719
Recordlength: 2758
Using only keys '0' of 5 possibly keys
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 1 8 unique ulonglong 1 1024
2 2265 80 multip. varchar prefix 336 1024
63 80 varchar 187
17 5 binary 1
1 8 ulonglong 1
3 1231 8 multip. ulonglong 10 1024
4 9 8 multip. ulonglong 840 1024
5 387 764 multip. ? prefix 1 4096
Field Start Length Nullpos Nullbit Type Huff tree Bits
1 1 8 zerofill(6) 1 9
2 9 8 zerofill(7) 1 9
3 17 5 1 9
4 22 5 1 9
5 27 12 blob 2 9
6 39 10 blob 3 9
7 49 4 always zero 1 9
8 53 10 blob 1 9
9 63 81 varchar 4 9
10 144 81 varchar 5 5
11 225 81 varchar 5 5
12 306 81 varchar 1 9
13 387 802 varchar 6 9
14 1189 10 blob 1 9
15 1199 10 blob 7 9
16 1209 5 1 9
17 1214 5 1 9
18 1219 12 blob 1 9
19 1231 8 no zeros, zerofill(6) 1 9
20 1239 1022 varchar 7 9
21 2261 4 always zero 1 9
22 2265 81 varchar 8 8
23 2346 402 varchar 2 9
24 2748 8 no zeros, zerofill(7) 1 9myisamchk -eiv /var/lib/mysql/test/posts
Checking MyISAM file: /var/lib/mysql/test/posts
Data records: 1680 Deleted blocks: 0
- check file-size
- check record delete-chain
No recordlinks
- check key delete-chain
block_size 1024:
block_size 2048:
block_size 3072:
block_size 4096:
- check index reference
- check data record references index: 1
Key: 1: Keyblocks used: 92% Packed: 0% Max levels: 2
- check data record references index: 2
Key: 2: Keyblocks used: 93% Packed: 90% Max levels: 2
- check data record references index: 3
Key: 3: Keyblocks used: 92% Packed: 0% Max levels: 2
- check data record references index: 4
Key: 4: Keyblocks used: 92% Packed: 0% Max levels: 2
- check data record references index: 5
Key: 5: Keyblocks used: 88% Packed: 97% Max levels: 2
Total: Keyblocks used: 91% Packed: 91%
- check records and index references
Records: 1680 M.recordlength: 4102 Packed: 0%
Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00
Record blocks: 1680 Delete blocks: 0
Record data: 6892064 Deleted data: 0
Lost space: 1284 Linkdata: 6264
User time 0.11, System time 0.00
Maximum resident set size 3036, Integral resident set size 0
Non-physical pagefaults 925, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 0, Involuntary context switches 74Status
Table status. One or more of analyzed, changed, crashed, open, optimized keys and sorted index pages.
Auto increment key
Index number of the table's auto-increment column. Not shown if no auto-increment column exists.
Last value
Most recently generated auto-increment value. Not shown if no auto-increment column exists.
Data records
Number of records in the table.
Deleted blocks
Number of deleted blocks that are still reserving space. Use OPTIMIZE TABLE to defragment.
Datafile parts
For dynamic tables, the number of data blocks. If the table is optimized, this will match the number of data records.
Deleted data
Number of bytes of unreclaimed deleted data, Use OPTIMIZE TABLE to reclaim the space.
Datafile pointer
Size in bytes of the data file pointer. The size of the data file pointer, in bytes.
Keyfile pointer
Size in bytes of the index file pointer.
Max datafile length
Maximum length, in bytes, that the data file could become.
Max keyfile length
Maximum length, in bytes, that the index file could become.
Recordlength
Space, in bytes, that each row takes.
table description
Description of all indexes in the table, followed by all columns
Key
Index number, starting with one. If not shown, the index is part of a multiple-column index.
Start
Where the index part starts in the row.
Len
Length of the index or index part. The length of a multiple-column index is the sum of the component lengths. Indexes of string columns is shorter than the full column length if only a string prefix is indexed.
Index
Whether an index value is unique or not. Either multip. or unique.
Type
Data type of the index of index part.
Rec/key
Record of the number of rows per value for the index or index part. Used by the optimizer to calculate query plans. Can be updated with myisamchk-a. If not present, defaults to 30.
Root
Root index block address.
Blocksize
Index block size, in bytes.
Field
Column number, starting with one. The first line will contain the position and number of bytes used to store NULL flags, if any (see Nullpos and Nullbit, below).
Start
Column's byte position within the table row.
Length
Column length, in bytes.
Nullpos
Byte containing the flag for NULL values. Empty if column cannot be NULL.
Nullbit
Bit containing the flag for NULL values. Empty if column cannot be NULL.
Type
Data type - see the table below for a list of possible values.
Huff tree
Only present for packed tables, contains the Huffman tree number associated with the column.
Bits
Only present for packed tables, contains the number of bits used in the Huffman tree.
no zeros
Zeros are not stored.
always zero
Zero values are stored with one bit.
Records
Number of records in the table.
M.recordlength
Average row length. For fixed rows, is the actual length of each row.
Packed
Percentage saving from stripping spaces from the end of strings.
Recordspace used
Percentage of the data file used.
Empty space
Percentage of the data file unused.
Blocks/Record
Average number of blocks per record. Values higher than one indicate fragmentation. Use OPTIMIZE TABLE to defragment.
Recordblocks
Number of used blocks. Will match the number of rows for fixed or optimized tables.
Deleteblocks
Number of deleted blocks
Recorddata
Used bytes in the data file.
Deleted data
Unused bytes in the data file.
Lost space
Total bytes lost, such as when a row is updated to a shorter length.
Linkdata
Sum of the bytes used for pointers linking disconnected blocks. Each is four to seven bytes in size.