Inspect a MyISAM FULLTEXT Index with myisam_ftdump

By the end of this guide you will be able to point myisam_ftdump at a MyISAM table, select its FULLTEXT index, and produce either a global report, per-word counts, length distribution or a raw index dump. The utility reads the index file directly, so this is an inspection task on the database host, not a remote SQL query.

Allow about 10 minutes if you already know the table path and its indexes. You need MariaDB's MyISAM client tools, read access to the table files, and enough space for any output you redirect. The examples below match MariaDB 10.11.14 on Ubuntu 24.04, installed as the mariadb-server package.

1. Confirm the tool and stop at the right boundary

Run the help command as your ordinary account. It does not alter a table or contact the server:

myisam_ftdump --help

Expected output starts like this:

Use: myisam_ftdump <table_name> <index_num>
  -h, --help          Display help and exit.
  -c, --count         Calculate per-word stats (counts and global weights).

The executable accepts a table name, a table name with the .MYI extension, or a path to the table's database directory. It must run on the host that contains the files. A name without a directory is resolved from your current working directory, so an apparently valid command can fail simply because you are in the wrong directory.

2. Make the index files consistent

If MariaDB is running and has the table open, ask it to flush its tables before reading the files. This requires a MariaDB account with permission to issue the statement, and may briefly affect table activity:

mariadb -e 'FLUSH TABLES'

Run that statement against the intended server, using your normal connection options if it is not the local default. Do not guess a socket, port or credentials. If you cannot identify the correct server, pause here and ask the database owner. The manpage specifically requires this flush before inspection when the server is running.

3. Find the FULLTEXT index number

The second argument is an index number, not an index name. Numbering starts at zero and follows the order in the table definition. For this table:

CREATE TABLE mytexttable (
  id  INT NOT NULL,
  txt TEXT NOT NULL,
  PRIMARY KEY (id),
  FULLTEXT (txt)
) ENGINE=MyISAM;

the primary key is index 0 and the FULLTEXT index is index 1. In a real schema, check the original CREATE TABLE statement or use a trusted database inspection workflow. Count every earlier primary, unique and ordinary key. Passing the wrong number is the most common distraction: the command can be syntactically correct while examining a different index.

4. Produce the default statistics report

Change to the database directory, or provide its full path. This example assumes the files are in /srv/mariadb/data/test:

cd /srv/mariadb/data/test
myisam_ftdump mytexttable 1

With no operation option, myisam_ftdump reports global index statistics. The program scans the entire index, so even a read-only report can take time on a large table. Do not treat a quiet terminal as a hang without checking the process and the table size.

You can avoid changing directory by supplying the table path:

myisam_ftdump /srv/mariadb/data/test/mytexttable 1

5. Choose a focused report

Use one of these operation options when the global report is not enough:

• --count or -c calculates per-word counts and global weights.
• --length or -l reports the distribution of word lengths.
• --dump or -d prints index entries with data offsets and word weights.
• --stats or -s explicitly requests the default global statistics report.
• --verbose or -v adds detail about what the program is doing.

For a frequency-oriented view, sort the count report in reverse order:

myisam_ftdump --count /srv/mariadb/data/test/mytexttable 1 | sort -r

The sort is performed by the shell after myisam_ftdump finishes producing each line. It does not change the index. Redirect a long report to a temporary file if you need to review it:

tmp_report=$(mktemp /tmp/myisam-ftdump.XXXXXX)
myisam_ftdump --dump /srv/mariadb/data/test/mytexttable 1 > "$tmp_report"
less "$tmp_report"
rm -- "$tmp_report"

The last command removes only that temporary report. The utility itself does not provide a repair or rebuild operation, and none of the examples above writes to the table. If the dump is still needed, copy it to an approved location before removing it; recovery after rm depends on your normal temporary-file and backup policy.

Common failure modes

Wrong host: a database client can connect remotely, but myisam_ftdump needs the local .MYI file. Log in to the server that stores the table before running it.

Wrong index number: index numbers begin at zero and include keys before the FULLTEXT key. Recheck the table definition instead of trying adjacent numbers at random.

Stale or busy files: run FLUSH TABLES first when the server is active. If another job is altering or copying the table, wait for it to finish. Do not stop a production service merely to make this read-only inspection convenient.

Large output: the complete index is scanned, and --dump can be especially verbose. Run it during an agreed maintenance window and redirect output rather than filling an interactive terminal.

Done means

• You confirmed the installed utility and its MariaDB version.
• You flushed tables when the server was running, or confirmed the files were not open.
• You identified the FULLTEXT index number from the table definition.
• You ran the report from the correct database directory or used an absolute table path.
• You chose the report type you need and verified that the command returned successfully.