Diagnose inefficient indexes to improve database performance - PolarDB

Index diagnostics identifies inefficient indexes in your PolarDB-X database—duplicate indexes, indexes with redundant partitioning rules, unused indexes, and low-selectivity indexes—and generates ready-to-run SQL statements to fix them. Removing or replacing these indexes reduces write overhead and reclaims storage without breaking query performance.

Prerequisites

Before you begin, ensure that you have:

A PolarDB-X instance running version 5.4.17-16859297 or later. To check your version, see View and update the version of an instance.
A database in AUTO mode or DRDS mode.

How index diagnostics works

Every index speeds up reads but adds overhead to writes and consumes storage. Over time, databases accumulate indexes that:

Duplicate each other — one index's columns are a prefix of another's, so both cover the same queries
Share the same partitioning rule as the primary table — making the global secondary index (GSI) redundant as a routing mechanism
Go unused — no query has touched them during the observed period
Have low selectivity — too many rows share the same index value, making the index ineffective for filtering

Index diagnostics scans both local indexes and global secondary indexes (GSIs) and outputs a two-step remediation plan for each problem it finds.

Run index diagnostics regularly to keep your database lean as your schema and query patterns evolve.

Syntax

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

Parameters

Parameter	Description	Default
`FULL`	Returns full diagnostic details (problem description + remediation steps). Without `FULL`, only remediation steps are returned.	Off
`FROM table_name`	Limits diagnostics to the specified table. Omit to diagnose all tables in the current database.	All tables
`MODE`	Diagnostics mode. See the table below.	`STATIC`

Diagnostics modes:

Mode	What it detects	Data collection required
`STATIC`	Structural problems detectable at index creation time — duplicate indexes, indexes with the same partitioning rule as the primary table	No
`DYNAMIC`	Runtime problems — unused indexes, low-selectivity indexes	Yes — collect data over a full business cycle before running
`MIXED`	Both structural and runtime problems	Yes — same requirement as `DYNAMIC`

Diagnose indexes in STATIC mode

STATIC mode detects structural issues immediately, without any data collection. Use it after schema changes to catch problems early.

Example: create a table and inspect its indexes

Create a table with several global indexes:

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 the inspection:

inspect full index from tb1\G

Output:

*************************** 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`);
*************************** 2. row ***************************
          SCHEMA: d5
           TABLE: tb1
           INDEX: idx_name
      INDEX_TYPE: GLOBAL INDEX
    INDEX_COLUMN: name
 COVERING_COLUMN: id
       USE_COUNT: 0
LAST_ACCESS_TIME: NULL
  DISCRIMINATION: 0.0
         PROBLEM: index columns duplicate: idx_name, idx_name_code;
  ADVICE (STEP1): alter table `tb1` alter index `idx_name` invisible;
  ADVICE (STEP2): alter table `tb1` drop index `idx_name`;
alter table `tb1` add local index `idx_name` (`name`);
*************************** 3. row ***************************
          SCHEMA: d5
           TABLE: tb1
           INDEX: idx_name_code
      INDEX_TYPE: GLOBAL INDEX
    INDEX_COLUMN: name,code
 COVERING_COLUMN: id
       USE_COUNT: 0
LAST_ACCESS_TIME: NULL
  DISCRIMINATION: 0.0
         PROBLEM: None
  ADVICE (STEP1): None
  ADVICE (STEP2): None
3 rows in set (0.42 sec)

Reading the output

The PROBLEM field describes the issue. The ADVICE fields provide a two-step remediation plan.

Output fields:

Field	Description
`PROBLEM`	Describes the specific issue with the index, or `None` if no issue is found
`ADVICE (STEP1)`	SQL to hide the index from the optimizer (safe, reversible)
`ADVICE (STEP2)`	SQL to drop or replace the index (permanent)
`DISCRIMINATION`	Selectivity score for the index
`USE_COUNT`	Number of times the index has been used since `GSI_STATISTICS_COLLECTION` was enabled
`LAST_ACCESS_TIME`	Timestamp of the last query that used this index

Findings for this example:

`idx_id` — flagged as ineffective because it uses the same partitioning rule as the primary table. The GSI adds no routing benefit and can be replaced with a cheaper local index.
`idx_name` — flagged as redundant because its indexed column (name) is already a prefix of idx_name_code (name, code). Any query that could use idx_name can use idx_name_code instead.
`idx_name_code` — no issues found.

Apply the remediation advice safely

The advice uses a two-step process to minimize risk.

Step 1 — hide the index (safe to reverse)

Mark the index invisible so the optimizer stops using it—without physically removing it:

alter table `tb1` alter index `idx_id` invisible;

Run your workload and verify that query performance is unchanged. For more information, see INVISIBLE INDEX.

Step 2 — drop or replace the index (permanent)

Once you've confirmed the index is safe to remove, run the Step 2 statement. For idx_id, Step 2 drops the GSI and adds a local index in its place:

alter table `tb1` drop index `idx_id`;
alter table `tb1` add local index `idx_id` (`id`);

Warning

Index deletion is permanent. Always complete Step 1 and validate your workload before running Step 2.

Diagnose indexes in DYNAMIC mode

DYNAMIC mode identifies indexes that are unused or have low selectivity at runtime. Because it relies on actual query traffic, you need to collect usage data before running diagnostics.

Collect usage data and run diagnostics

Enable index usage statistics collection:
Enabling GSI_STATISTICS_COLLECTION reduces query performance by approximately 1%. Disable it after diagnostics are complete.
```
set global GSI_STATISTICS_COLLECTION=true;
```
Let the database run through a complete business cycle—typically one full day or one full week, depending on your traffic patterns. This ensures the statistics reflect all normal query patterns, including low-frequency operations.
Run index diagnostics in DYNAMIC mode:
```
inspect full index mode=dynamic\G
```
Review the output and apply the remediation advice using the same two-step process as STATIC mode.

Disable statistics collection:

set global GSI_STATISTICS_COLLECTION=false;

When to use DYNAMIC mode

Scenario	When to run
Pre-stress-test tuning	Before stress testing begins — identify and remove inefficient indexes so they don't skew results
Production optimization	After collecting a full business cycle of traffic — adjust indexes based on real usage patterns

View global index usage with INFORMATION_SCHEMA

The INFORMATION_SCHEMA.GLOBAL_INDEXES view lets you inspect raw usage data for all GSIs in the current database. Use it to spot unused or oversized indexes before running a full diagnostics pass, or to verify the data quality before running DYNAMIC mode.

select * from information_schema.global_indexes where table="tb1"\G