PolarDB:INVISIBLE INDEX

Prerequisites

Before you begin, make sure that:

• Your PolarDB-X instance is version 5.4.17-16835173 or later. To check your version, see View and update the version of an instance

Usage notes

• Invisible indexes are supported in databases in AUTO mode and DRDS mode.

• Only global secondary indexes (GSIs) support invisible indexes. Local indexes are not supported.

• Changing index visibility does not affect index maintenance. The index data continues to update when the primary table data changes, regardless of whether the index is VISIBLE or INVISIBLE.

• If an index has a unique constraint, changing its visibility does not affect the constraint.

• If you set an index to INVISIBLE and use a FORCE INDEX hint on it in a query, the hint has no effect.

Recommended workflow

Use this workflow to safely evaluate whether an index can be dropped:

1. Set the index to INVISIBLE.

2. Monitor your workload for impact — see Evaluate impact below.

3. If you notice degraded performance, set the index back to VISIBLE immediately.

Manage index visibility

The following examples use AUTO mode table syntax.

Create a table with a visible index (default)

If you omit the VISIBLE or INVISIBLE keyword, the index is visible by default.

create table t_order (
  `id`       bigint(11),
  `order_id` varchar(20),
  `buyer_id` varchar(20),
  global index `g_order_id` (order_id) partition by key(order_id)
) partition by hash(`id`);

Create a table with an invisible index

create table t_order (
  `id`       bigint(11),
  `order_id` varchar(20),
  `buyer_id` varchar(20),
  global index `g_order_id` (order_id) partition by key(order_id) invisible
) partition by hash(`id`);

Add an invisible index to an existing table

alter table t_order add global index g_buyer_id(buyer_id) partition by hash(buyer_id) invisible;

Change the visibility of an existing index

-- Set the index to invisible
alter table t_order alter index `g_order_id` invisible;

-- Set the index back to visible
alter table t_order alter index `g_order_id` visible;

Evaluate impact

After setting an index to INVISIBLE, check the following to assess whether the index is needed:

• Slow queries: Check whether new slow queries appear in the slow query log that were not there before.

• FORCE INDEX hints: If your application uses FORCE INDEX on the now-invisible index, those queries no longer benefit from the hint and may slow down.

• CPU and memory: If the workload is unchanged, check whether CPU and memory usage on the PolarDB-X instance increases significantly.

• Stress tests: If you run a load test, check whether the test results differ significantly from the baseline.

FAQ

After I set an index to INVISIBLE, all queries that used it are affected. How do I test the impact on a single query without changing the index visibility?

Use the IGNORE INDEX hint to instruct the optimizer to skip a specific index for that query only, without modifying the index visibility.