SHOW FULLTEXT INDEX STATUS

Syntax

SHOW FULLTEXT INDEX STATUS FOR <table_name>;

Arguments

• <table_name>: The name of the table on which a full-text index is being created.

Output

As shown in the example output, the SHOW FULLTEXT INDEX STATUS command outputs two values for each node, partition pair: Progress (%) and Broken (%).

• The Progress (%) is the percentage of rows in the table's columnstore segments that have been indexed.

• The Broken (%) is the percentage of rows in the table's columnstore segments that have an associated broken index.

+-----------+---------+--------------+------------+
| Partition | Node ID | Progress (%) | Broken (%) |
+-----------+---------+--------------+------------+
| 4         | 11      | 100.00       | 0.00       |
| 3         | 10      |  75.00       | 0.00       |
| 3         | 11      |  71.00       | 0.00       |
| 5         | 11      |  43.00       | 0.00       |
| 2         | 11      |  11.00       | 2.00       |
....

Remarks

• Use the SHOW FULLTEXT INDEX STATUS command to monitor the progress of index creation.

  • In SingleStore, a full-text index is built for each columnstore segment. This command outputs the status of the per-segment indexes.

• The Progress (%) and Broken (%) in the output are calculated as the percentage of rows in the on-disk columnstore segments for a partition, node pair which have been indexed.

• To run the SHOW FULLTEXT INDEX STATUS command, the user must have the SHOW METADATA, SELECT, or INDEX permission on the table.

• The SHOW FULLTEXT INDEX STATUS command displays rows from the MV_FULLTEXT_INDEX_STATUS information schema view. This view contains the full-text indexing status of all the tables from all the databases in the cluster at the partition level.

Example 1 - Successful Index Creation

This example uses the vecs table from the Hybrid Search - Re-ranking and Blending Searches page.

After creating the vecs table and loading data into that table, run the following commands.

ALTER TABLE vecs ADD FULLTEXT USING VERSION 2 ft_para(paragraph);

SHOW FULLTEXT INDEX STATUS FOR vecs;

+-----------+---------+--------------+------------+
| Partition | Node ID | Progress (%) | Broken (%) |
+-----------+---------+--------------+------------+
| 4         | 11      | 100.00       | 0.00       |
| 3         | 10      | 100.00       | 0.00       |
| 3         | 11      | 100.00       | 0.00       |
| 5         | 11      | 100.00       | 0.00       |
| 2         | 11      | 100.00       | 0.00       |
| 0         | 10      | 100.00       | 0.00       |
| 1         | 10      | 100.00       | 0.00       |
| 1         | 11      | 100.00       | 0.00       |
| 7         | 11      | 100.00       | 0.00       |
| 6         | 11      | 100.00       | 0.00       |
| 0         | 11      | 100.00       | 0.00       |
| 2         | 10      | 100.00       | 0.00       |
+-----------+---------+--------------+------------+

As the vecs table is small, the index creation is fast and the output shows that all per-segment indexes were built successfully. If the vecs table were larger and the partitions contained multiple columnstore segments, the Progress (%) column would show percentages less than 100% during the index build.

Example 2 - Broken Index

It is possible for the full-text index creation for a columnstore segment to fail. In this case, the Broken (%) column indicates the percentage of the rows in a partition, node pair that have an associated broken index. An example of such output follows.

SHOW FULLTEXT INDEX STATUS FOR <table_name>;

+-----------+---------+--------------+------------+
| Partition | Node ID | Progress (%) | Broken (%) |
+-----------+---------+--------------+------------+
| 1         | 10      | 100.00       | 100.00     |
| 1         | 11      | 100.00       | 100.00     |
| 2         | 11      | 100.00       |   0.00     |
| 2         | 10      | 100.00       |   0.00     |
+-----------+---------+--------------+------------+

To attempt to fix a broken index, run the OPTIMIZE TABLE … FIX_FULLTEXT command.

OPTIMIZE TABLE <table_name> FIX_FULLTEXT;

Refer to Full-Text Index Status for additional information on fixing broken full-text indexes.