Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 39.3Mb
PDF (A4) - 39.3Mb
PDF (RPM) - 38.6Mb
HTML Download (TGZ) - 11.1Mb
HTML Download (Zip) - 11.1Mb
HTML Download (RPM) - 9.8Mb
Man Pages (TGZ) - 212.5Kb
Man Pages (Zip) - 321.6Kb
Info (Gzip) - 3.6Mb
Info (Zip) - 3.6Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  myisam_ftdump — Display Full-Text Index information

4.6.2 myisam_ftdump — Display Full-Text Index information

myisam_ftdump displays information about FULLTEXT indexes in MyISAM tables. It reads the MyISAM index file directly, so it must be run on the server host where the table is located. Before using myisam_ftdump, be sure to issue a FLUSH TABLES statement first if the server is running.

myisam_ftdump scans and dumps the entire index, which is not particularly fast. On the other hand, the distribution of words changes infrequently, so it need not be run often.

Invoke myisam_ftdump like this:

shell> myisam_ftdump [options] tbl_name index_num

The tbl_name argument should be the name of a MyISAM table. You can also specify a table by naming its index file (the file with the .MYI suffix). If you do not invoke myisam_ftdump in the directory where the table files are located, the table or index file name must be preceded by the path name to the table's database directory. Index numbers begin with 0.

Example: Suppose that the test database contains a table named mytexttable that has the following definition:

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

The index on id is index 0 and the FULLTEXT index on txt is index 1. If your working directory is the test database directory, invoke myisam_ftdump as follows:

shell> myisam_ftdump mytexttable 1

If the path name to the test database directory is /usr/local/mysql/data/test, you can also specify the table name argument using that path name. This is useful if you do not invoke myisam_ftdump in the database directory:

shell> myisam_ftdump /usr/local/mysql/data/test/mytexttable 1

You can use myisam_ftdump to generate a list of index entries in order of frequency of occurrence like this on Unix-like systems:

shell> myisam_ftdump -c mytexttable 1 | sort -r

On Windows, use:

shell> myisam_ftdump -c mytexttable 1 | sort /R

myisam_ftdump supports the following options:

  • --help, -h -?

    Display a help message and exit.

  • --count, -c

    Calculate per-word statistics (counts and global weights).

  • --dump, -d

    Dump the index, including data offsets and word weights.

  • --length, -l

    Report the length distribution.

  • --stats, -s

    Report global index statistics. This is the default operation if no other operation is specified.

  • --verbose, -v

    Verbose mode. Print more output about what the program does.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by yangqing yang on June 15, 2018
1### create the myisam table with fulltext index
mysql> CREATE DATABASE test_schema;
Query OK, 1 row affected (0.00 sec)

mysql> USE test_schema;
Database changed

mysql> CREATE TABLE IF NOT EXISTS test SELECT * FROM mysql.help_topic;
Query OK, 643 rows affected (0.28 sec)
Records: 643 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE test ENGINE = MyISAM;
Query OK, 643 rows affected (0.02 sec)
Records: 643 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE test ADD FULLTEXT KEY ft_url (`url`);
Query OK, 643 rows affected (0.02 sec)
Records: 643 Duplicates: 0 Warnings: 0

2#### change into the directory where test.MYI file locates in
[root@oscar test_schema]# pwd
/var/lib/mysql/test_schema
[root@oscar test_schema]# ls -ltr
total 764
-rw-r----- 1 mysql mysql 61 Jun 15 18:05 db.opt
-rw-r----- 1 mysql mysql 8770 Jun 15 18:06 test.frm
-rw-r----- 1 mysql mysql 708184 Jun 15 18:06 test.MYD
-rw-r----- 1 mysql mysql 55296 Jun 15 18:06 test.MYI

3### let's start our experiments

#--experiment 1
[root@oscar test_schema]# myisam_ftdump --count test 0|sort -nr|head
641 -5.7698823 refman
641 -5.7698823 mysql
641 -5.7698823 http
641 -5.7698823 html
411 -0.5718558 functions
68 2.1348623 string
65 2.1851866 time
65 2.1851866 date
50 2.4731714 spatial
42 2.6609253 property

<take the firt line as an example.>
641: this means the WORD "refman" occurs 641 times in the column`url`,
-5.7698823: its "global weights" is -5.7698823 (i don't know what it really means and does)

#--experiment 2
[root@oscar test_schema]# myisam_ftdump --dump test 0|head
19434 0.9354537 alter
23028 0.9354537 alter
37880 0.9354537 alter
399c8 0.9354537 alter
417d8 0.9354537 alter
7ad54 0.9254975 alter
7c648 0.9354537 alter
94a4c 0.9354537 alter
9742c 0.9354537 alter
a60b8 0.9354537 alter

<take the firt line as an example.>
19434: each index data offsets
0.9354537: word weights

#--experiment 3
[root@oscar test_schema]# myisam_ftdump --length test 0
4: 288 26.89% 288 26.9%
5: 87 8.12% 375 35.0%
6: 181 16.90% 556 51.9%
7: 133 12.42% 689 64.3%
8: 153 14.29% 842 78.6%
9: 49 4.58% 891 83.2%
10: 89 8.31% 980 91.5%
11: 25 2.33% 1005 93.8%
12: 41 3.83% 1046 97.7%
13: 21 1.96% 1067 99.6%
18: 4 0.37% 1071 100.0%

<take the firt line as an example.>
for 4 characters long word,288 in total,26.89 percentage
375=288+87
35.0%=26.89% + 8.12%

#--experiment 4
[root@oscar test_schema]# myisam_ftdump --stats test 0
Total rows: 643
Total words: 1071
Unique words: 183
Longest word: 18 chars (geometrycollection)
Median length: 6
Average global weight: 5.278736
Most common word: 641 times, weight: -5.769882 (html)

[root@oscar test_schema]# myisam_ftdump -d test 0|wc -l
1071

[root@oscar test_schema]# myisam_ftdump -c test 0|wc -l
183
Sign Up Login You must be logged in to post a comment.