MySQL 8.0 Invisible Indexes for Safe Query Plan Testing

What Invisible Indexes Actually Do

An invisible index in MySQL 8.0 is fully maintained by InnoDB — it's built, updated on every INSERT/UPDATE/DELETE, and occupies storage space — but the optimizer ignores it when choosing execution plans. From the optimizer's perspective, it doesn't exist. From InnoDB's perspective, it's a normal index that must be kept consistent with the table data.

This distinction matters for understanding the use cases. Making an index invisible is cheap — it's a metadata change, no data movement, completes in milliseconds. The index continues to consume write overhead on every DML operation. Making an index visible again is equally instant. This is what enables the testing workflows described below.

-- Create an index as invisible (optimizer won't use it)
CREATE INDEX idx_orders_status_date ON orders (status, created_at)
  INVISIBLE;

-- Make an existing index invisible
ALTER TABLE orders ALTER INDEX idx_orders_customer_id INVISIBLE;

-- Make an invisible index visible again
ALTER TABLE orders ALTER INDEX idx_orders_customer_id VISIBLE;

-- Check index visibility for a table
SELECT index_name, column_name, seq_in_index, is_visible
FROM information_schema.statistics
WHERE table_schema = DATABASE()
  AND table_name = 'orders'
ORDER BY index_name, seq_in_index;

-- MySQL 8.0.23+: use_invisible_indexes optimizer switch
-- (applies to the current session only)
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
-- Now the optimizer CAN use invisible indexes in this session
-- Useful for testing: see what plans look like with the invisible index active

Use Case 1: Testing a New Index Before Making It Production-Visible

The standard workflow for adding a new index to a production table: create the index as invisible, validate query plans in a test session, make it visible after confirming no plan regressions.

-- Step 1: Create the candidate index invisible
-- InnoDB builds it fully but optimizer ignores it
ALTER TABLE orders ADD INDEX idx_orders_region_status (region, status, created_at)
  INVISIBLE;

-- Step 2: In a test session, enable use of invisible indexes
SET SESSION optimizer_switch = 'use_invisible_indexes=on';

-- Step 3: Verify the new index is used for the target query
EXPLAIN FORMAT=TREE
SELECT order_id, customer_id, total_amount
FROM orders
WHERE region = 'west'
  AND status = 'pending'
  AND created_at > NOW() - INTERVAL 30 DAY;
-- Should show idx_orders_region_status in the plan

-- Step 4: Check no other queries are negatively affected
-- Run EXPLAIN for your top 20 queries against this table
-- Confirm the optimizer doesn't switch to the new index for queries
-- where it would perform worse

-- Step 5: If validation passes, make the index visible
ALTER TABLE orders ALTER INDEX idx_orders_region_status VISIBLE;

-- Step 6: Monitor for 24-48 hours
-- Check slow query log for any regressions
-- Check index usage
SELECT index_name, rows_selected, rows_inserted, rows_updated, rows_deleted
FROM sys.schema_index_statistics
WHERE table_schema = DATABASE()
  AND table_name = 'orders'
ORDER BY rows_selected DESC;

Use Case 2: Safe Index Retirement

Index retirement is where invisible indexes provide the most value. The problem before MySQL 8.0: you think an index is unused (based on information_schema.statistics usage counters), but dropping it immediately risks breaking a query you missed. The index usage counters reset on server restart, and a query that runs monthly won't show up in a two-week analysis window.

The invisible index retirement workflow eliminates this risk:

-- Step 1: Identify candidate indexes for retirement
-- Unused = zero rows_selected over the observation period
SELECT
  t.table_schema,
  t.table_name,
  t.index_name,
  s.rows_selected,
  s.rows_inserted,
  s.rows_updated,
  s.rows_deleted,
  t.index_type,
  t.non_unique
FROM information_schema.statistics t
LEFT JOIN sys.schema_index_statistics s
  ON s.table_schema = t.table_schema
  AND s.table_name = t.table_name
  AND s.index_name = t.index_name
WHERE t.table_schema = 'app_db'
  AND t.seq_in_index = 1
  AND t.index_name != 'PRIMARY'
  AND (s.rows_selected IS NULL OR s.rows_selected = 0)
ORDER BY t.table_name, t.index_name;

-- Step 2: Make candidate indexes invisible (NOT drop them)
ALTER TABLE orders ALTER INDEX idx_orders_old_status INVISIBLE;
ALTER TABLE customers ALTER INDEX idx_customers_legacy_ref INVISIBLE;

-- Step 3: Monitor for 2-4 weeks (cover your longest reporting cycle)
-- Watch for slow query log entries referencing these tables
-- Watch for application errors suggesting plan regressions

-- Step 4: If no issues after observation period, drop
ALTER TABLE orders DROP INDEX idx_orders_old_status;

-- If issues appear, re-enable instantly with no data rebuild
ALTER TABLE orders ALTER INDEX idx_orders_old_status VISIBLE;
-- Index is immediately available to optimizer -- zero rebuild cost

This workflow is the reason invisible indexes exist. The alternative — dropping and recreating a large index — means a full index rebuild that can take minutes to hours on a multi-hundred-GB table. Rolling back an invisible-to-visible change takes milliseconds.

Use Case 3: Plan Regression Testing Before Index Changes

When you're planning to drop an index as part of a schema cleanup, you want to know exactly which queries will be affected and how their plans will change. Invisible indexes let you answer that question before committing to the change.

-- Simulate dropping idx_orders_customer_date by making it invisible
ALTER TABLE orders ALTER INDEX idx_orders_customer_date INVISIBLE;

-- Now run EXPLAIN for all queries in your critical path
-- that touch the orders table
-- The plans you see now are what you'll get after dropping the index

-- Useful: capture plan summaries before and after
SELECT
  digest_text,
  count_star,
  avg_timer_wait / 1000000000 AS avg_latency_ms,
  sum_rows_examined / count_star AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text LIKE '%orders%'
ORDER BY sum_timer_wait DESC
LIMIT 20;

-- Make invisible, wait 15 minutes for the summary to reflect new plans
-- then compare avg_rows_examined -- a dramatic increase indicates plan regression

Use Case 4: Index A/B Testing in Production

For databases with complex query mixes where index changes have unpredictable cross-query effects, invisible indexes enable genuine A/B testing of index configurations. Run with Index A visible for one week, make A invisible and B visible for the next week, compare performance_schema metrics.

-- A/B test setup: compare composite index strategies
-- Option A: (customer_id, status, created_at)
CREATE INDEX idx_option_a ON orders (customer_id, status, created_at) INVISIBLE;

-- Option B: (status, customer_id, created_at)
CREATE INDEX idx_option_b ON orders (status, customer_id, created_at) INVISIBLE;

-- Week 1: test Option A
ALTER TABLE orders ALTER INDEX idx_option_a VISIBLE;
-- Run workload, capture performance_schema.events_statements_summary_by_digest

-- Week 2: switch to Option B
ALTER TABLE orders ALTER INDEX idx_option_a INVISIBLE;
ALTER TABLE orders ALTER INDEX idx_option_b VISIBLE;
-- Run same workload, capture metrics again

-- Compare: which index produces lower avg_timer_wait across the full query mix?
-- Not just the target query -- the entire workload hitting that table

Limitations to Know

Invisible indexes have two constraints that occasionally surprise teams:

PRIMARY KEY indexes cannot be made invisible. The primary key is always visible to the optimizer. This makes sense — InnoDB's clustered index structure means every secondary index lookup references the primary key. Making it invisible would break the storage engine's fundamental access path.

The only index on a column used in a foreign key constraint cannot be made invisible. MySQL requires an index that can enforce the FK constraint to be visible at all times. If you try to make such an index invisible, you'll get an error. The workaround: create a duplicate index, make the original invisible — but that defeats the purpose of retiring the index.

-- This will fail if idx_customer_id is the only index on customer_id
-- and customer_id is referenced by a foreign key
ALTER TABLE orders ALTER INDEX idx_customer_id INVISIBLE;
-- ERROR: Cannot make index invisible because it is the only index
-- that can enforce the referenced foreign key constraint

-- Workaround: create a covering index that also covers the FK column
CREATE INDEX idx_customer_id_covering ON orders (customer_id, status);
-- Now idx_customer_id can be made invisible because idx_customer_id_covering
-- can enforce the FK constraint

Operationalizing Invisible Indexes: The 30-Day Retirement Process

For production databases, codify the invisible index workflow into a standard operating procedure. The process we use:

1. Week 1 — Identify candidates. Query sys.schema_index_statistics for indexes with zero or near-zero rows_selected over the last 30 days. Cross-reference with the slow query log to ensure the analysis period covered all batch cycles.
2. Week 2 — Make candidates invisible. One index at a time, not all at once. Document each change with a timestamp.
3. Weeks 3-5 — Observe. Monitor slow query log, application error rates, and events_statements_summary_by_digest for affected tables. Cover at least one full monthly batch cycle.
4. Week 6 — Drop or restore. If no degradation observed, drop the invisible index with a standard schema change. If degradation appeared at any point, make the index visible again and remove it from the retirement list.

This process lets you retire 10-15 indexes on a busy production database in a six-week cycle with essentially zero risk of a performance incident. Without invisible indexes, the same process requires either accepting the risk of immediate drops or building a staging environment that replicates production data volume — neither of which most teams have capacity for.