Ask Claude to Suggest Database Indexes Based on Your Query Patterns

Missing indexes are the most common cause of slow database queries, and redundant indexes silently slow down every write. Claude reads your actual query patterns and suggests exactly which indexes to add, remove, or change.

Read all database queries in src/repositories/ and the Eloquent/ORM calls 
in src/models/. Suggest indexes based on the WHERE clauses, JOIN conditions, 
and ORDER BY columns. Generate the migration to add them.

Claude traces every query path and maps columns to their index needs:

Suggested indexes:
+ orders(customer_id, status) — used in WHERE customer_id = ? AND status = ?
+ products(category_id, created_at) — used in WHERE category_id = ? ORDER BY created_at
+ users(email) UNIQUE — used in WHERE email = ? (login, password reset)

Redundant indexes to remove:
- orders(customer_id) — covered by the composite index above
- products(created_at) — rarely queried alone, covered by composite

Missing from common queries:
⚠ users(last_login_at) — the dashboard sorts all users by last login 
  but there's no index, causing a full table scan on every page load

Target specific index optimization needs:

# Analyze slow queries
Read the slow query log (or the queries flagged by your monitoring tool) 
and suggest indexes that would fix each one.

# Check for over-indexing
List every index on every table. Flag indexes that overlap with other 
indexes or that exist on columns that are never used in WHERE or JOIN clauses.

# Composite index ordering
Check if any composite indexes have their columns in the wrong order. 
The most selective column should usually come first.

# Partial and conditional indexes
For tables with soft deletes, suggest partial indexes that exclude 
deleted rows — smaller indexes that only cover the rows queries actually need.

The right index turns a 5-second query into 5 milliseconds — let Claude read your queries and tell you exactly which columns need indexing.

via Claude Code