Once my users table crossed 10 million rows, slowdowns started creeping in. I ran EXPLAIN everywhere, added index after index — then one day I looked back and realized I was sitting on dozens of indexes that queries never actually touched. That’s when I got serious about cleaning up unused indexes.
Why Are Unused Indexes a Problem?
A lot of people assume indexes are always a win: faster queries, easier lookups. But every index you create comes with a cost:
- Slower writes: Every
INSERT,UPDATE, orDELETEforces MySQL to update all related indexes. Five redundant indexes means five extra updates on every write. - Wasted disk space: Indexes live on disk just like real data. A 50GB table with bloated indexes can easily eat up an unnecessary 10–20GB.
- Buffer pool pollution: InnoDB’s buffer pool caches index pages. Unused indexes crowd out pages that actually matter, tanking your cache hit rate.
- Query optimizer confusion: The optimizer has to evaluate more execution plans, and sometimes picks the wrong index as a result.
At production scale with tens of millions of rows, this is not something you can afford to ignore.
Prerequisites: Enable Performance Schema
MySQL tracks index usage through Performance Schema. First, check whether it’s already enabled:
SHOW VARIABLES LIKE 'performance_schema';If the result shows OFF, enable it in your config file /etc/mysql/mysql.conf.d/mysqld.cnf (or my.cnf, depending on your distro):
[mysqld]
performance_schema = ONThen restart MySQL:
sudo systemctl restart mysqlNote: Performance Schema adds a small memory overhead (~5–10%), but for most production servers that’s entirely acceptable. MySQL 5.7 and later have it enabled by default.
Make sure index lookup instrumentation is active:
-- Check index instrumentation status
SELECT * FROM performance_schema.setup_consumers
WHERE NAME LIKE '%statements%';
-- Enable if not already enabled
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME = 'events_statements_history_long';How to Find Unused Indexes
Method 1: Use the sys Schema (Fastest)
MySQL 5.7.7+ ships with the sys.schema_unused_indexes view — a single query gives you everything you need:
SELECT *
FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'sys', 'information_schema')
ORDER BY object_schema, object_name;Typical output looks like this:
+---------------+-------------+------------------+
| object_schema | object_name | index_name |
+---------------+-------------+------------------+
| myapp | users | idx_users_phone |
| myapp | orders | idx_orders_ref |
| myapp | logs | idx_logs_level |
+---------------+-------------+------------------+I found idx_users_phone — an index created back when a phone number search feature still existed, but the feature had been removed long ago while the index quietly kept consuming resources. This kind of thing is extremely common in long-lived projects.
Method 2: Detailed Query from Performance Schema
If you want row count information to help prioritize what to tackle first, combine performance_schema with information_schema:
SELECT
t.TABLE_SCHEMA AS db_name,
t.TABLE_NAME AS table_name,
s.INDEX_NAME AS index_name,
s.COLUMN_NAME AS column_name,
t.TABLE_ROWS AS approx_rows
FROM information_schema.STATISTICS s
JOIN information_schema.TABLES t
ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
AND s.TABLE_NAME = t.TABLE_NAME
WHERE s.TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'sys', 'information_schema')
AND s.INDEX_NAME != 'PRIMARY'
AND CONCAT(s.TABLE_SCHEMA, '.', s.TABLE_NAME, '.', s.INDEX_NAME) NOT IN (
SELECT CONCAT(object_schema, '.', object_name, '.', index_name)
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star > 0
)
ORDER BY t.TABLE_ROWS DESC;This query adds estimated row counts, letting you prioritize dropping indexes on larger tables first since the impact there is significantly higher.
Method 3: Detect Redundant Indexes (Duplicates)
Beyond unused indexes, there are also redundant indexes — indexes MySQL doesn’t need because another index already covers them:
SELECT *
FROM sys.schema_redundant_indexes
WHERE table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema');A classic example: you have a composite index on (user_id, created_at), then someone also creates a standalone index on (user_id) — the latter is completely redundant because the composite index already covers that prefix.
Analyze and Decide Before Dropping
Don’t rush to drop an index just because its name appears in the list. Here’s the checklist I always run through before dropping any index:
1. Confirm the Observation Window Is Long Enough
Performance Schema data resets every time MySQL restarts. If the server was restarted two days ago, an index used only for monthly reports will incorrectly show up as “unused.”
-- Check how long MySQL has been running (in seconds)
SHOW GLOBAL STATUS LIKE 'Uptime';
-- 2592000 seconds = 30 daysI typically wait at least 30 days, ideally 90 days, to cover quarterly batch jobs.
2. Search the Application Code
# Search for the index name in the codebase (in case FORCE INDEX hints are used)
grep -r "idx_users_phone" /var/www/myapp/
# Look for hardcoded index hints
grep -r "FORCE INDEX\|USE INDEX\|IGNORE INDEX" /var/www/myapp/ --include="*.php"3. Back Up the Index Definition Before Dropping
-- Export the CREATE TABLE statement as a backup
SHOW CREATE TABLE users\GSave the output to a file. If you ever need to recreate the index later, you’ll have the exact statement ready without having to reverse-engineer the structure.
4. Drop Indexes Safely with ALGORITHM=INPLACE
On MySQL 5.6+, use ALGORITHM=INPLACE, LOCK=NONE to avoid table locks while traffic is running:
-- Drop a single index
ALTER TABLE users
DROP INDEX idx_users_phone,
ALGORITHM=INPLACE,
LOCK=NONE;
-- Drop multiple indexes at once (fewer rebuilds)
ALTER TABLE orders
DROP INDEX idx_orders_ref,
DROP INDEX idx_orders_old_status,
ALGORITHM=INPLACE,
LOCK=NONE;If the table is especially large (50GB+) and you need absolute zero downtime, consider pt-online-schema-change from Percona Toolkit:
pt-online-schema-change \
--alter "DROP INDEX idx_users_phone" \
--execute \
D=myapp,t=usersVerification & Monitoring After Dropping
After dropping, monitor for at least 24–48 hours to make sure nothing is negatively affected.
Compare Write Performance
SELECT
OBJECT_NAME,
COUNT_WRITE,
ROUND(SUM_TIMER_WRITE / 1000000000, 2) AS write_time_ms,
ROUND(AVG_TIMER_WRITE / 1000000, 2) AS avg_write_us
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = 'myapp'
ORDER BY SUM_TIMER_WRITE DESC
LIMIT 10;Check Reclaimed Disk Space
SELECT
TABLE_NAME,
ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS total_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'myapp'
ORDER BY INDEX_LENGTH DESC;Automated Weekly Monitoring Script
#!/bin/bash
# check_unused_indexes.sh
MYSQL_USER="monitor_user"
MYSQL_PASS="your_password"
DB="myapp"
UNUSED=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "
SELECT COUNT(*) FROM sys.schema_unused_indexes
WHERE object_schema = '$DB';" 2>/dev/null | tail -1)
if [ "$UNUSED" -gt "0" ]; then
echo "ALERT: $UNUSED unused indexes found in $DB" | \
mail -s "MySQL Unused Index Alert" [email protected]
fi# Add to crontab to run every Monday at 9 AM
0 9 * * 1 /opt/scripts/check_unused_indexes.shReal-World Results
After cleaning up indexes on a production database with a 12-million-row users table, here’s what I measured:
- Index size dropped from 4.2GB to 2.8GB — a 33% reduction
- Average INSERT time on the users table improved by roughly 18%
- InnoDB buffer pool hit rate improved slightly as index pages no longer crowded out useful data
Your exact numbers will vary depending on your schema and workload, but the principle holds universally: an index that’s never used is pure overhead with zero benefit. Regular cleanup is a standard part of database maintenance that junior engineers tend to skip — not because it’s hard, but because they don’t know the tools are already built into MySQL.
The natural next step after clearing out unused indexes is evaluating whether the indexes you do use are actually effective — that’s a conversation about EXPLAIN ANALYZE and index selectivity, which I’ll cover in a future post.
