The Index You Can Turn Off: MySQL 8 Invisible Indexes

What Is an Invisible Index?

Think of a light switch. The bulb isn't burned out. The wires are still there. You just flipped it off. Flip it back on anytime — instant light.

An invisible index is an index that:

• Physically exists (takes up space, gets updated on every INSERT/UPDATE/DELETE)
• Is ignored by the query optimizer when building execution plans
• Can be toggled instantly with a single SQL command

-- Hide the index from the optimizer
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;

-- Bring it back
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;

No rebuild. No downtime. No drama. Just instant.

---

Three Scenarios Where This Changes Everything

🔍 Scenario 1: Safe Index Removal (My Exact Problem)

Instead of dropping first and praying, you hide first and observe:

-- Step 1: hide the index
ALTER TABLE orders ALTER INDEX idx_old_status INVISIBLE;

-- Step 2: monitor for a few days — slow query log, dashboards, alerts
-- Step 3a: everything's fine → drop it for real
ALTER TABLE orders DROP INDEX idx_old_status;

-- Step 3b: something breaks → restore in under a second
ALTER TABLE orders ALTER INDEX idx_old_status VISIBLE;

The difference between "delete and hope" and "disable and observe" is the difference between confidence and anxiety.

---

🧪 Scenario 2: A/B Testing Index Strategies

You have a slow query. You have two index candidates. Which one wins?

-- Create both, keep one invisible
CREATE INDEX idx_v1 ON orders (status, created_at);
CREATE INDEX idx_v2 ON orders (created_at, status) INVISIBLE;

-- Test with idx_v1 active
EXPLAIN SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;

-- Swap them — zero rebuild time
ALTER TABLE orders ALTER INDEX idx_v1 INVISIBLE;
ALTER TABLE orders ALTER INDEX idx_v2 VISIBLE;

-- Test with idx_v2 active
EXPLAIN SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;

Clean, controlled experiment on your live database — no migration scripts, no staging guesswork.

---

🚨 Scenario 3: Emergency Index Kill Switch

Occasionally the optimizer picks a "bad" index and tanks performance. Before invisible indexes, your options were grim: wait it out, use query hints everywhere, or drop the index entirely.

-- The optimizer chose poorly — kill it without dropping
ALTER TABLE orders ALTER INDEX idx_causing_problems INVISIBLE;
-- Queries immediately re-plan without this index

Now you have breathing room. Investigate at your own pace, then decide: drop it permanently, or turn it back on once you understand the root cause.

---

Under the Hood: What's Actually Happening

┌─────────────────────────────────────────────────────┐
│                  MySQL 8 Query Flow                  │
│                                                       │
│  SQL Query                                            │
│      │                                                │
│      ▼                                                │
│  ┌─────────────┐                                      │
│  │  Optimizer  │ ──► sees VISIBLE indexes only        │
│  └─────────────┘                                      │
│         │                                             │
│         ▼                                             │
│    Execution Plan                                     │
│                                                       │
│  ┌────────────────────────────────────────────────┐  │
│  │            Storage Engine (InnoDB)              │  │
│  │                                                 │  │
│  │  idx_status   [VISIBLE]   ◄── used in queries   │  │
│  │  idx_old      [INVISIBLE] ◄── maintained,       │  │
│  │                                but never chosen  │  │
│  └────────────────────────────────────────────────┘  │
└─────────────────────────────────────────────────────┘

The crucial detail: InnoDB keeps invisible indexes fully up to date. Every write still updates them. This is exactly why toggling back to VISIBLE is instant — there's no rebuild needed because the data was never stale.

The cost: invisible indexes still consume disk space and add write overhead. They're a temporary tool, not a permanent hiding place.

---

Inspect Your Index Visibility

SELECT 
    TABLE_NAME,
    INDEX_NAME,
    IS_VISIBLE,
    INDEX_TYPE,
    COLUMN_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_db'
ORDER BY TABLE_NAME, INDEX_NAME;

Sample output:

+------------+----------------+------------+------------+-------------+
| TABLE_NAME | INDEX_NAME     | IS_VISIBLE | INDEX_TYPE | COLUMN_NAME |
+------------+----------------+------------+------------+-------------+
| orders     | PRIMARY        | YES        | BTREE      | id          |
| orders     | idx_status     | YES        | BTREE      | status      |
| orders     | idx_old_stuff  | NO         | BTREE      | legacy_col  |
+------------+----------------+------------+------------+-------------+

IS_VISIBLE = NO — that's your invisible index, sitting quietly in the shadows.

---

The Hidden Superpower: Force-Enable Invisible Indexes Per Session

Here's a trick most people don't know: you can make invisible indexes visible for a single session — without affecting anyone else.

-- Enable invisible indexes for this session only
SET SESSION optimizer_switch = 'use_invisible_indexes=on';

-- Now EXPLAIN will factor in invisible indexes
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

-- Reset when done
SET SESSION optimizer_switch = 'use_invisible_indexes=off';

Use this to benchmark an invisible index before turning it on. You can see exactly what query plans look like with and without it — production traffic untouched.

---

Create an Index as Invisible from the Start

Useful when you're pre-building infrastructure for a feature that isn't live yet:

-- Add the index silently — no optimizer impact yet
CREATE INDEX idx_new_feature ON orders (user_id, status) INVISIBLE;

-- Deploy your application code
-- Verify everything looks right in staging/canary
-- Then flip the switch
ALTER TABLE orders ALTER INDEX idx_new_feature VISIBLE;

It's like laying the tracks before the train arrives.

---

Find Your Deletion Candidates (Then Make Them Invisible First)

MySQL's sys schema can surface indexes that haven't been used since the last server restart:

SELECT 
    object_schema,
    object_name,
    index_name
FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema');

Got a list? Resist the urge to drop them immediately.

Make them invisible. Wait a week. Watch your slow query log. Check your APM dashboards. If nothing screams — then drop.

---

The New Mental Model

Old workflow:

Suspect index is unused → Drop it → Hope for the best → 3 AM phone call

New workflow:

Suspect index is unused
  → ALTER ... INVISIBLE
  → Monitor for 3–7 days
  → Check slow_query_log, metrics, alerts
  → All clear? DROP INDEX
  → Something broke? VISIBLE in one second

It's a small feature. But it shifts you from reactive to deliberate when managing schema changes — and that shift is worth a lot at 3 AM.

---

Quick Reference

Action	SQL
Hide index	ALTER TABLE t ALTER INDEX idx INVISIBLE;
Show index	ALTER TABLE t ALTER INDEX idx VISIBLE;
Create hidden	CREATE INDEX idx ON t (col) INVISIBLE;
Test in session	SET SESSION optimizer_switch = 'use_invisible_indexes=on';
Find invisible	SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE IS_VISIBLE = 'NO';
Find unused	SELECT * FROM sys.schema_unused_indexes;

Available since MySQL 8.0. No plugin required. No configuration flag. Just SQL.