Database.Middle.How would you detect missing indexes?

Detecting missing indexes is key to optimizing database performance — especially for slow queries or high CPU usage.

Let me walk you through several ways to detect them in PostgreSQL, MySQL, and general techniques.

✅ 1. Use the Query Planner (EXPLAIN)

You can check how a query is executed by the database. If it scans entire tables instead of using an index — that’s a red flag.

🔍 PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'stanley@example.com';

Look for "Seq Scan" = full table scan ❌

If you had an index on email, you’d see "Index Scan" ✅

🔍 MySQL:

EXPLAIN SELECT * FROM users WHERE email = 'stanley@example.com';

If type = ALL → full table scan ❌

If type = ref or const → index is used ✅

🚦 2. Check Slow Query Logs

Both PostgreSQL and MySQL can log slow queries. These are likely missing indexes.

PostgreSQL:

Enable in postgresql.conf:

log_min_duration_statement = 500  # log queries slower than 500ms

MySQL:

Enable slow query log:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;

Then inspect the log and EXPLAIN those queries.

🧠 3. Use Built-in Index Advisor Tools

🔧 PostgreSQL (auto-explain + pg_stat_statements):

• pg_stat_statements helps you find frequently run or expensive queries.
• You can pair this with auto_explain to detect missing indexes.

SELECT *
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Then EXPLAIN those queries.

🧠 SQL Server (DMVs):

SELECT *
FROM sys.dm_db_missing_index_details;

🧠 MySQL (Performance Schema):

MySQL 8+ can suggest indexes through the sys.schema_index_statistics or by looking at performance_schema.events_statements_summary_by_digest.

---

🔬 4. Use 3rd-Party Tools or Extensions

Tool	DB Support	Description
pg_stat_statements	PostgreSQL	Shows expensive queries
HypoPG	PostgreSQL	Simulates indexes without creating them
pt-query-digest	MySQL (Percona)	Analyzes slow query logs
pgBadger	PostgreSQL	Parses logs and gives index suggestions
EXPLAIN ANALYZE Buffers	PostgreSQL	Show block reads for potential indexing needs

⚠️ Common Signs You’re Missing an Index

• Full table scan on WHERE/ORDER BY columns
• High CPU usage with low I/O
• Frequent use of queries like:
  • WHERE email = ?
  • JOIN ON foreign_key
  • ORDER BY created_at DESC
• Repeated slow queries from the same pattern

---

🛠️ What to Do When You Detect One

1. Identify the column(s) used in:
   • WHERE
   • JOIN
   • ORDER BY
2. Create an index:

CREATE INDEX idx_users_email ON users(email);

Test again with EXPLAIN ANALYZE

✅ Summary

Method	Description
EXPLAIN	Check query plans manually
Slow query logs	Find high-latency queries
Statistics extensions	Analyze frequent queries (e.g. pg_stat)
3rd-party tools	Deeper automated insights
Common patterns	Detect joins and filters without indexes