Help Center/ TaurusDB/ Kernel/ DDL Optimization/ Progress Queries for Creating Secondary Indexes
Updated on 2026-01-15 GMT+08:00
View PDF
Share

Progress Queries for Creating Secondary Indexes

Scenarios

When PFS is disabled, creating indexes in a production environment can take a lot of time. To help you track DDL progress, this feature displays progress for time-consuming index creation operations even after performance schema has been disabled.

Constraints

  • The kernel version of your TaurusDB instance must be 2.0.51.240300 or later. For details about how to check the kernel version, see How Can I Check the Version of a TaurusDB Instance?
  • This feature only displays progress for creating secondary indexes, but not for creating spatial indexes, creating full-text indexes, or other DDL operations.

Functions

This feature is enabled by default. When an index is being created for a table, you can obtain the index creation progress by querying the INFORMATION_SCHEMA.INNODB_ALTER_TABLE_PROGRESS table.

Figure 1 Table structure
Table 1 INNODB_ALTER_TABLE_PROGRESS table information

Column

Description

THREAD_ID

Thread ID.

QUERY

SQL statement delivered by the client for creating an index.

START_TIME

Time when the SQL statement for creating an index is delivered.

ELAPSED_TIME

Amount of time (seconds) that has already been used.

ALTER_TABLE_PHASE

Current phase.

WORK_COMPLETED

Amount of work that has been completed so far.

WORK_ESTIMATED

An estimate of the total amount of work required for the entire index creation process.

TIME_REQUIRED

An estimate of how much more time (seconds) is needed.

WORK_ESTIMATED and TIME_REQUIRED will be adjusted continuously throughout the index creation process, so they do not change linearly.

Examples

  1. Run the following SQL statement to query the structure of a table: 

    desc table_name;

    Example:

    Query the structure of table test_stage.

    desc test_stage;
    Figure 2 Viewing the table structure

    Table test_stage does not have a secondary index, as indicated by its structure.

  2. Run the following SQL statement to add an index for a column in the table: 

    ALTER TABLE table_name ADD INDEX idxa(field_name);

    Example:

    Add an index to column a in table test_stage.

    ALTER TABLE test_stage ADD INDEX idxa(a);

  3. Run the following SQL statement to query the index creation progress: 

    SELECT QUERY, ALTER_TABLE_PHASE FROM  INFORMATION_SCHEMA.INNODB_ALTER_TABLE_PROGRESS;
    Figure 3 Querying the index creation progress

Parent Topic: DDL Optimization