How to query inefficient indexes

Query examples

• View the usage of all indexes:

  SELECT * FROM information_schema.logical_index_usage;

• Find indexes that have never been used (TOTAL_USAGE = 0):

  SELECT * FROM information_schema.logical_index_usage
  WHERE TOTAL_USAGE = 0;

• Filter by logical table name:

  SELECT * FROM information_schema.logical_index_usage
  WHERE LOGICAL_TABLE = 'my_table';

• Filter by index name:

  SELECT * FROM information_schema.logical_index_usage
  WHERE INDEX_NAME = 'idx_col_b';

• Filter by logical database name:

  SELECT * FROM information_schema.logical_index_usage
  WHERE LOGICAL_SCHEMA = 'my_database';

• Combine filters to query a specific index on a specific table:

  SELECT * FROM information_schema.logical_index_usage
  WHERE LOGICAL_TABLE = 'my_table' AND INDEX_NAME = 'idx_col_b';

• Find the usage of local indexes on GSIs:

  SELECT * FROM information_schema.logical_index_usage
  WHERE GSI_NAME != '' AND GSI_NAME IS NOT NULL;

• Sort by usage to find the hottest or coldest indexes:

  SELECT LOGICAL_TABLE, INDEX_NAME, TOTAL_USAGE, AVG_USAGE, PARTITION_COUNT
  FROM information_schema.logical_index_usage
  ORDER BY TOTAL_USAGE DESC;

• Find inefficient indexes (many partitions but low usage):

  SELECT LOGICAL_TABLE, INDEX_NAME, TOTAL_USAGE, PARTITION_COUNT, AVG_USAGE
  FROM information_schema.logical_index_usage
  WHERE PARTITION_COUNT > 4 AND AVG_USAGE < 10
  ORDER BY AVG_USAGE ASC;

Query examples

• View the usage of all indexes on each physical partition:

  SELECT * FROM information_schema.physical_index_usage;

• Find indexes that have never been used on any physical partition (COUNT_STAR = 0):

  SELECT * FROM information_schema.physical_index_usage
  WHERE COUNT_STAR = 0;

• Filter by index name:

  SELECT * FROM information_schema.physical_index_usage
  WHERE INDEX_NAME = 'idx_col_b';

• Filter by logical table name:

  SELECT * FROM information_schema.physical_index_usage
  WHERE LOGICAL_TABLE = 'my_table';

• Filter by logical database name:

  SELECT * FROM information_schema.physical_index_usage
  WHERE LOGICAL_SCHEMA = 'my_database';

• View index statistics on GSI physical tables:

  SELECT
      OBJECT_SCHEMA,
      OBJECT_NAME,
      INDEX_NAME,
      COUNT_STAR,
      LOGICAL_SCHEMA,
      LOGICAL_TABLE
  FROM information_schema.physical_index_usage
  WHERE LOGICAL_SCHEMA = 'my_database'
    AND LOGICAL_TABLE LIKE 'gsi_name%'
  ORDER BY COUNT_STAR DESC
  LIMIT 100

Syntax

INSPECT [FULL] INDEX [FROM table_name] [MODE={STATIC|DYNAMIC|MIXED}]

Parameters

• MODE: Specifies the diagnosis mode. Three modes are supported.

  • STATIC (default): Diagnoses static issues such as duplicate indexes. These issues can be identified immediately after an index is created.

  • DYNAMIC: Diagnoses dynamic issues such as low index discrimination or indexes that have never been used. These issues can only be discovered after the index has been in use for a period of time. (Requires global index usage statistics to be enabled.)

  • MIXED: Diagnoses both static and dynamic issues. The MIXED mode also requires index usage data to be collected over a period of time for accurate diagnosis results. (Requires global index usage statistics to be enabled.)

• FROM: Specifies the table.

  • If the FROM clause is omitted, all indexes in the current database are diagnosed by default.

  • If the FROM clause is specified, only the indexes on the specified table are diagnosed.

• FULL: Specifies the detail level of the output.

  • With the FULL option, detailed diagnosis results are returned, including identified issues and suggestions.

  • Without the FULL option, only suggestions are returned.

Query examples

• For example, diagnose the following table tb1:

  CREATE TABLE tb1(
    id int,
    name varchar(20),
    code varchar(50),
    primary key(id),
    global index idx_name(`name`) partition by key(name),
    global index idx_name_code(`name`, `code`) partition by key(name, code),
    global index idx_id(`id`) partition by key(id)
  ) partition by key(id);

• Run INSPECT FULL INDEX MODE=MIXED\G to get the MIXED mode diagnosis results:

  *************************** 1. row ***************************
            SCHEMA: d5
             TABLE: tb1
             INDEX: idx_id
        INDEX_TYPE: GLOBAL INDEX
      INDEX_COLUMN: id
   COVERING_COLUMN:
         USE_COUNT: 0
  LAST_ACCESS_TIME: NULL
    DISCRIMINATION: 0.0
           PROBLEM: ineffective gsi `idx_id` because it has the same rule as primary table
    ADVICE (STEP1): alter table `tb1` alter index `idx_id` invisible;
    ADVICE (STEP2): alter table `tb1` drop index `idx_id`;
  alter table `tb1` add local index `idx_id` (`id`);

  • PROBLEM describes the issues with the index, such as the same partitioning rule as the primary table, duplicate index columns with other indexes, or USE_COUNT of 0 indicating the index has never been used.

  • The ADVICE field provides two-step optimization suggestions:

    • STEP1: Hide the index by using an invisible index to evaluate the business impact.

    • STEP2: Delete the index after confirming no impact (or rebuild a better index).

    To avoid affecting your business, make sure to execute STEP1 for evaluation before executing the deletion in STEP2.