Index health decides whether your database scales quietly or falls over during peak traffic. This guide walks through the exact process for auditing index health across production systems, MySQL, PostgreSQL, MongoDB, or MariaDB, without guesswork.

TL;DR

• Auditing database index health means checking usage, cardinality, bloat, and query alignment across every production instance.
• Unused indexes past a 30-day window are the first thing to drop; they cost write throughput for zero read benefit.
• Run performance_schema or pg_stat_user_indexes queries monthly, not once a year, to catch drift before it compounds.
• Compliance-heavy environments (PCI-DSS, fintech, healthcare) need index audits documented, not just performed.
• Mydbops treats index audits as a recurring remote DBA task, not a one-time consulting engagement.

Why this matters

A database with the wrong indexes doesn't fail loudly. It slows down 5-10% a month until a query that ran in 40ms in January takes 4 seconds by June, and nobody can point to the exact commit that caused it. Index rot is cumulative and silent, which is exactly why it needs a scheduled audit instead of a reactive one.

Teams running MySQL, PostgreSQL, MongoDB, TiDB, or MSSQL in production in 2026 are also carrying more compliance weight than they did five years ago. If your database sits inside a PCI-DSS or SOC 2 scope, index changes need a paper trail, not a Slack message. Mydbops runs this exact audit cycle for clients across compliance-heavy sectors, and the pattern is consistent: most index bloat comes from ORMs auto-generating indexes nobody asked for, plus indexes left behind after a schema migration. For teams in regulated sectors, the sequencing matters even more — see how database consulting for compliance-heavy industries typically structures this work before an audit window opens.

What you'll need

• Read access to performance_schema (MySQL/MariaDB) or pg_stat_user_indexes (PostgreSQL)
• A staging or read-replica environment to test index drops safely
• Percona Toolkit or pt-index-usage for MySQL environments
• pgstattuple extension enabled for PostgreSQL bloat checks
• Query logs or slow query log covering at least 7 days of production traffic
• A change-management ticket template to document every index add/drop
• 2-4 hours per instance for a first-pass audit; less on repeat runs

The steps

1. Pull index usage statistics from every instance

Start by querying which indexes are actually being read. In MySQL 8, performance_schema.table_io_waits_summary_by_index_usage shows read/write counts per index since the last server restart. In PostgreSQL, pg_stat_user_indexes.idx_scan gives the same signal. Any index with zero scans over a 30-day production window is a candidate for removal — not an automatic drop, just a flag.

Common mistake: running this check right after a server restart, when counters reset to zero and every index looks unused. Always confirm uptime before trusting the numbers.

2. Flag duplicate and redundant indexes

A composite index on (user_id, created_at) makes a standalone index on user_id redundant in most query patterns. pt-duplicate-key-checker (part of Percona Toolkit) finds these automatically for MySQL and MariaDB. For PostgreSQL, cross-reference pg_indexes definitions by hand or with pg_stat_user_indexes joined against pg_index.

Redundant indexes double your write cost for a table without adding any read benefit. On a table taking 500 writes/second, every extra index adds measurable latency to each insert and update.

3. Check cardinality and selectivity

An index on a status column with three possible values (active, pending, closed) rarely helps the optimizer — cardinality is too low to narrow a scan meaningfully. Run SHOW INDEX FROM table_name in MySQL and look at the Cardinality column, or SELECT n_distinct FROM pg_stats WHERE tablename = 'table_name' in PostgreSQL.

Low-cardinality indexes on high-write tables are a common source of wasted overhead. If cardinality is under roughly 5% of row count, the index is likely not earning its keep.

4. Measure fragmentation and bloat

InnoDB pages fragment as rows are updated and deleted over time, and B-tree indexes bloat the same way. Run ANALYZE TABLE in MySQL to refresh statistics, then compare DATA_LENGTH and INDEX_LENGTH in information_schema.tables against expected row counts. For PostgreSQL, pgstattuple reports dead tuple percentage directly.

Expected outcome: fragmentation under 10-15% is healthy. Above that, an OPTIMIZE TABLE (MySQL) or VACUUM FULL (PostgreSQL) during a maintenance window recovers the space — but never run either on a live primary during peak hours.

5. Validate index-to-query alignment with EXPLAIN

Pull your 20 slowest queries from the slow query log and run EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) against each one. Look for full table scans (type: ALL in MySQL, Seq Scan in PostgreSQL) where an index should be doing the work.

This step catches the opposite problem from step 1 — queries that need an index that doesn't exist yet, versus indexes that exist but nothing uses.

6. Check compliance and change-control requirements

If any of the audited databases fall under PCI-DSS, SOC 2, or HIPAA scope, every index add or drop needs a documented reason and a rollback plan. This isn't optional paperwork — auditors ask for it directly. The process for structuring this documentation is covered in detail in how to prepare a database for a PCI-DSS compliance audit, which walks through the evidence trail regulators expect.

Common mistake: dropping an index in staging, confirming no regression, then dropping it in production without a documented change ticket. When the auditor asks six months later why a schema changed, nobody remembers.

7. Set a re-audit cadence

A one-time audit degrades in value the moment new code ships. Set a recurring cadence — monthly for high-write OLTP systems, quarterly for read-heavy analytical workloads — and automate the usage-statistics pull with a cron job or scheduled query.

Troubleshooting

Index usage shows zero but the query still feels slow. Check whether the optimizer is choosing a different index or a full scan entirely — a zero-usage index isn't the bottleneck if it's not even in the query plan.

Cardinality statistics look stale. Run ANALYZE TABLE (MySQL) or ANALYZE (PostgreSQL) before trusting any cardinality number — auto-analyze thresholds can leave statistics days or weeks out of date on high-write tables.

Dropping an index in staging causes a regression that didn't show in EXPLAIN. Staging data volume rarely matches production. Test index drops against a production-sized read replica, not a staging environment with 1% of the row count.

Fragmentation keeps returning after OPTIMIZE TABLE. High delete-and-reinsert workloads re-fragment fast. Consider partitioning the table instead of repeated maintenance windows, especially for logistics or e-commerce platforms with heavy order-table churn.

Index audit takes too long to repeat manually every month. Script steps 1-4 into a single query bundle and schedule it — manual re-runs are the main reason audits lapse after the first quarter.

Tools and resources

• Percona Toolkit (pt-index-usage, pt-duplicate-key-checker) for MySQL and MariaDB
• pgstattuple and pg_stat_user_indexes for PostgreSQL
• MySQL Shell's util.checkForServerUpgrade() for pre-upgrade index compatibility checks
• Slow query log analysis via pt-query-digest or pgBadger
• For teams scaling fast, managed database services for SaaS startups covers how index audits fit into a broader operational cadence when engineering headcount is thin

What to do next

Once the audit surfaces action items, prioritize by write-cost savings first, then by query latency improvement. Index drops on high-write tables usually pay off within days; new index additions on slow queries pay off immediately, since the win is measurable on the next EXPLAIN run.

FAQ

How often should you audit database index health?

Audit high-write OLTP databases monthly and read-heavy analytical systems quarterly in 2026. Waiting longer than a quarter lets unused and redundant indexes accumulate past the point of easy cleanup.

What's the fastest way to find unused indexes in MySQL?

Query performance_schema.table_io_waits_summary_by_index_usage and look for zero read/write counts over a 30-day window. Confirm server uptime first, since a recent restart resets these counters to zero.

Is dropping an unused index always safe?

No — test the drop against a production-sized read replica before touching the primary. Some indexes support constraint checks or replication filters that don't show up in simple usage counts.

How much does index bloat cost in performance?

Fragmentation above 10-15% typically slows range scans and increases page reads noticeably. The exact cost depends on table size and access pattern, which is why EXPLAIN validation matters more than a single bloat percentage.

Do PostgreSQL and MySQL index audits use the same process?

The steps are the same conceptually — check usage, cardinality, bloat, and query alignment — but the system tables differ. MySQL uses performance_schema and information_schema; PostgreSQL uses pg_stat_user_indexes and pgstattuple.

What does a database index audit cost if outsourced?

Costs vary by instance count and compliance scope, so check current rates directly with a managed database provider. A remote DBA retainer typically bundles index audits into ongoing performance monitoring rather than billing them as a separate one-off.

Can index audits catch compliance gaps?

Yes — PCI-DSS and SOC 2 scopes require documented change history for schema modifications, including index changes. An audit that skips documentation leaves a gap auditors flag immediately.

Should index audits run on the primary or a replica?

Run usage-statistics queries on the primary since replicas don't always reflect identical query patterns, but test any index drop or bloat-repair operation on a replica first. This keeps production read/write latency untouched during the audit itself.

One last thing

The single biggest index-health mistake in 2026 isn't a missing index — it's an index nobody remembers adding. ORM frameworks and old migration scripts leave indexes behind long after the query that needed them is gone, and those forgotten indexes quietly tax every write to the table. Audit for age and origin, not just usage count.

Related guides

• Managed database services for fintech platforms
• Managed database services for e-commerce platforms
• Remote DBA services for logistics companies

Conclusion

Index health is not a one-time cleanup. Usage counts, redundancy, cardinality, bloat and query alignment all drift as new code ships, which is why the audit works best as a scheduled routine with a documented change trail rather than a reactive fire drill.

Start where the payoff is fastest: unused and redundant indexes on high-write tables, confirmed against uptime and tested on a production-sized replica. Then work outward to EXPLAIN validation on your slowest queries, and keep every add or drop tied to a change ticket so the next compliance review has its evidence ready.

For teams running MySQL, PostgreSQL, MongoDB or MariaDB fleets without spare DBA hours, Mydbops folds this audit cycle into ongoing remote DBA support and 24/7 managed services, so index reviews happen on schedule instead of after the next slowdown.