Progress Queries for Creating Secondary Indexes

Constraints

• The kernel version of your GaussDB(for MySQL) instance is 2.0.51.240300 or later.
• 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

• THREAD_ID: the thread ID
• QUERY: the statement delivered by the client to create an index
• START_TIME: the time when the command for creating an index is delivered
• ELAPSED_TIME: the amount of time that has already been used
• ALTER_TABLE_PHASE: the current phase
• WORK_COMPLETED: the 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 is needed
• WORK_ESTIMATED and TIME_REQUIRED will be adjusted continuously throughout the index creation process, so they do not change linearly.

Example

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