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 (%) value is the percentage of rows in the table's columnstore segments that have been indexed.

• The Broken (%) value is the percentage of rows in the table's columnstore segments that have an associated broken index. This value does not represent the percentage of indexes that failed.

+-----------+---------+--------------+------------+
| 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 (%) values in the output are calculated from rows in the on-disk columnstore segments for each node-partition pair.

• 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 - Reranking Full-Text and Vector Search Results 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

Creating a full-text index for a columnstore segment can fail. If it does, the index is marked as broken, and searches that use the index return an error explaining why it was marked as broken, such as an out-of-memory error during indexing. 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:

OPTIMIZE TABLE <table_name> FIX_FULLTEXT;

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