MySQL Invisible Indexes: Test a Drop Before You Commit to It

Every long-lived schema accumulates indexes nobody remembers adding. Some back a query that was deleted three refactors ago. Some duplicate the leading columns of a composite index and earn their keep only in a developer’s imagination. They are not free: each one slows down writes, bloats the buffer pool, and adds work to every INSERT and UPDATE. The obvious fix is to drop them, but dropping an index on a large table is a one-way door. If you guessed wrong, rebuilding it can lock up a busy table for minutes while you scramble.

MySQL’s invisible indexes turn that one-way door into a light switch. You can hide an index from the optimizer, watch what happens to your queries, and flip it back if anything degrades, all with fast in-place operations. If you maintain a MySQL schema behind a PHP application, this is one of the most useful low-risk tools in the box, and it is easy to wire into a normal Laravel migration workflow.

What “Invisible” Actually Means

An invisible index is a fully maintained index that the optimizer simply refuses to use. From the MySQL manual: an invisible index continues to be updated as table rows change, and a unique index still prevents duplicate inserts, regardless of whether the index is visible or invisible. The only thing that changes is whether the query planner is allowed to consider it when building an execution plan.

That distinction is the whole point. Because the index is still physically present and up to date, making it visible again is instant. You are not rebuilding anything. You are just telling the optimizer it may look at the index again.

The feature applies to any index other than a primary key, explicit or implicit. It has been available since MySQL 8.0 and works the same way in the current 9.x releases.

Making an Index Invisible

Indexes are visible by default. You control visibility with the VISIBLE and INVISIBLE keywords in CREATE TABLE, CREATE INDEX, or ALTER TABLE:

CREATE INDEX idx_articles_status ON articles (status) INVISIBLE;

To flip an existing index, use ALTER TABLE ... ALTER INDEX:

ALTER TABLE articles ALTER INDEX idx_articles_status INVISIBLE;
ALTER TABLE articles ALTER INDEX idx_articles_status VISIBLE;

Both of those are metadata-only changes. Contrast that with DROP INDEX followed by CREATE INDEX on a multi-million-row table, which has to scan and sort the whole column. As the manual puts it, dropping and re-adding an index can be expensive for a large table, whereas making it invisible and visible are fast, in-place operations.

You can confirm the current state through SHOW INDEX or the Information Schema:

SELECT INDEX_NAME, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'phparch' AND TABLE_NAME = 'articles';

The Workflow: Prove an Index Is Dead Before Deleting It

Here is the pattern I reach for whenever I suspect an index is unused on a production table.

First, make it invisible. The optimizer immediately stops using it, but the index is still there as a safety net.

Second, let real traffic run against the table for a meaningful window. A day covers most daily cycles; a week covers the weekly reporting jobs that only fire on Mondays. The manual lists exactly what to watch for if the index turns out to matter: queries that suddenly show different EXPLAIN plans, an increase in workload visible in Performance Schema, queries showing up in the slow query log that were not there before, and errors from any query that used an index hint naming the now-invisible index.

Third, decide. If nothing regressed, the index was dead weight and you can drop it for real with confidence. If something slowed down, flip it back to visible instantly and you have lost nothing.

If you want to check the counterfactual without waiting, you can ask the optimizer to consider invisible indexes for a single query using the SET_VAR hint. This is perfect for confirming that a specific query really does depend on the index you are about to hide:

EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */
    id, title FROM articles WHERE status = 'published';

There is also a server-wide use_invisible_indexes flag inside optimizer_switch, off by default, but scoping it to one query with SET_VAR is far safer than toggling it globally.

Driving It From a Laravel Migration

Laravel’s schema builder does not yet expose a first-class method for index visibility, so the clean approach is a raw statement inside a migration. This keeps the change versioned, reviewable, and reversible like any other schema change:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        DB::statement('ALTER TABLE articles ALTER INDEX idx_articles_status INVISIBLE');
    }

    public function down(): void
    {
        DB::statement('ALTER TABLE articles ALTER INDEX idx_articles_status VISIBLE');
    }
};

Because up() and down() are both instant metadata operations, this migration is genuinely safe to run and roll back on a live database. Ship it, observe, and only when you are satisfied does a follow-up migration call $table->dropIndex('idx_articles_status'). You get a real audit trail of the decision instead of a risky manual DROP typed into a production console at the end of a long day.

One Sharp Edge Worth Knowing

There is a rule that catches people out. A primary key can never be made invisible, and neither can an index that is acting as an implicit primary key. If a table has no explicit primary key but does have a UNIQUE index on NOT NULL columns, MySQL treats the first such index as the effective primary key, and trying to hide it fails:

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;
ERROR 3522 (HY000): A primary key index cannot be invisible.

Add a real primary key and that unique index is demoted back to an ordinary index, at which point it can be made invisible like any other. The takeaway is a familiar one anyway: give every table an explicit primary key.

Small Feature, Real Confidence

Invisible indexes will not show up in a conference keynote. But the ability to run a controlled experiment on your schema, on production, with an instant undo, changes how you approach index cleanup. Instead of arguing about whether an index is safe to remove, you hide it, watch the slow query log and your EXPLAIN plans for a week, and let the data settle the question. For anyone maintaining a MySQL database that has been around long enough to collect cruft, that is a habit worth building.