Database Indexing Strategy AI Prompts for DBAs

TL;DR

AI prompts accelerate index analysis by generating candidate indexes from query patterns
Index consolidation prevents the index bloat that slows writes and bloats storage
Different index types serve different access patterns; match type to query
Index maintenance requires ongoing monitoring as data volumes and query patterns evolve
Query rewrite hints often outperform adding indexes

Introduction

Database indexing often feels like witchcraft. Add an index here because the query looks slow. Remove an index there because inserts started crawling. Hope the query optimizer makes good choices. Yet the difference between a well-indexed database and a chaotic one can mean the difference between sub-second responses and timeout failures that tank business metrics.

The challenge is that indexing decisions interact in complex ways. Adding an index helps one query but slows all inserts on that table. A composite index helps some queries but hurts others. The query optimizer makes cost-based decisions that can be unpredictable. And as data volumes grow and query patterns shift, yesterday’s optimal indexing strategy becomes tomorrow’s liability.

AI changes the indexing analysis workflow. When structured prompts guide index analysis, DBAs can systematically evaluate query patterns, identify coverage gaps, and develop consolidated indexing strategies that balance read and write performance.

This guide provides AI prompts designed specifically for DBAs who want to optimize database indexing. These prompts address index analysis, type selection, maintenance planning, and performance troubleshooting.

Indexing Fundamentals
Query Analysis and Index Selection
Index Types and When to Use Each
Composite Index Design
Index Consolidation
Index Maintenance Planning
Performance Troubleshooting
Cloud and Modern Database Considerations
FAQ: Database Indexing Excellence
Conclusion

Indexing Fundamentals

Understanding Index Mechanics

Understanding how indexes work internally clarifies why certain decisions work.

Prompt for Index Mechanics:

Explain database index mechanics for [DATABASE TYPE/VERSION]:

Index storage structures:

1. **B-Tree indexes** (default in most databases):
   - Balanced tree structure
   - O(log n) search complexity
   - Range scan efficiency
   - Default choice for most cases

2. **Hash indexes**:
   - Key-value hash lookup
   - O(1) lookup for equality
   - No range scans
   - Memory-intensive

3. **Bitmap indexes**:
   - Bit array for low-cardinality columns
   - Efficient for WHERE IN (multiple values)
   - Concurrency challenges
   - Best for data warehousing

4. **Full-text indexes**:
   - Text search optimization
   - Inverted index structure
   - Ranking algorithms
   - Language-specific stemming

5. **GiST and GIN** (PostgreSQL):
   - Geometric and Range types
   - Custom indexing for special types
   - JSON/JSONB indexing
   - Full-text search

Index storage concepts:
- Clustered vs. non-clustered
- Leaf nodes and internal nodes
- Index fragmentation
- Fill factor and page splits
- Free page index

For your database:
- Default index type
- Available index types
- Limitations and constraints
- Monitoring capabilities

Generate index mechanics overview for your environment.

Index Benefits and Costs

Every index is a trade-off between read performance and write overhead.

Prompt for Cost-Benefit Analysis:

Analyze index cost-benefit for [TABLE/QUERIES]:

Benefits of indexes:

1. **Read optimization**:
   - Faster WHERE clause filtering
   - Efficient JOIN operations
   - Reduced full table scans
   - Covered queries (index-only)

2. **Sort optimization**:
   - ORDER BY acceleration
   - GROUP BY efficiency
   - DISTINCT optimization

3. **Constraint enforcement**:
   - Primary key enforcement
   - Unique constraint checking
   - Referential integrity

Costs of indexes:

1. **Write overhead**:
   - Every INSERT requires index updates
   - Every UPDATE requires index updates (if indexed columns change)
   - DELETE overhead multiplied by index count
   - Transaction log growth

2. **Storage overhead**:
   - Index size often 20-30% of table size
   - Multiple indexes compound this
   - Clustered index includes data

3. **Maintenance overhead**:
   - Index fragmentation
   - Statistics updates
   - Rebuuild operations

4. **Optimizer confusion**:
   - More indexes can confuse optimizer
   - Statistics staleness
   - Plan instability

For your table and workload:
- Estimated write overhead per index
- Estimated storage increase
- Read benefit assessment

Generate cost-benefit analysis with recommendation.

Query Analysis and Index Selection

Query Pattern Identification

Index selection starts with understanding the queries that run against your data.

Prompt for Query Pattern Analysis:

Analyze query patterns for [TABLE/WORKLOAD]:

Query collection methods:
- Query log review
- Query store (SQL Server)
- Performance schema (MySQL)
- pg_stat_statements (PostgreSQL)
- CloudWatch/Cloud SQL metrics

Query pattern classification:

1. **Point queries** (exact match):
   - WHERE id = 12345
   - Uses: Primary key or unique index
   - Efficiency: O(log n) with B-Tree

2. **Range queries**:
   - WHERE created_at > '2024-01-01'
   - WHERE amount BETWEEN 100 AND 200
   - Needs: B-Tree index with range column

3. **Prefix/match queries**:
   - WHERE name LIKE 'John%'
   - Uses: B-Tree or specialized prefix index

4. **Complex filters**:
   - WHERE a = 1 AND b > 2 AND c IN (1,2,3)
   - Needs: Composite index or index combination

5. **JOIN patterns**:
   - INNER JOIN ON orders.customer_id = customers.id
   - Needs: Foreign key indexes, join column indexes

Query frequency analysis:
- High-frequency queries (run constantly)
- Medium-frequency (hourly/daily)
- Low-frequency (ad-hoc, reporting)

For your workload:
- Pattern classification for top queries
- Index opportunities identified
- Query priorities

Generate query pattern analysis with index recommendations.

Index Candidate Generation

Generate index candidates based on query analysis.

Prompt for Index Candidate Generation:

Generate index candidates for [TABLE with slow queries]:

Top queries requiring optimization:

**Query 1: [QUERY TEXT]**
- Frequency: [HOW OFTEN]
- Priority: [HIGH/MEDIUM/LOW]
- Current plan: [IF KNOWN]

Candidate indexes:
1. Index on [COLUMNS] - covers [COLUMNS IN SELECT/WHERE/JOIN]
2. Partial index on [CONDITION] - for filtered queries
3. Include columns [COLUMNS] - for covering index

**Query 2: [QUERY TEXT]**
- Same structure...

Index evaluation criteria:
- Selectivity: < 5% selectivity usually needed
- Column order: EQ first, range last for composite
- Included columns: for covering indexes
- Partial index condition: WHERE clause filter

Conflict detection:
- Existing indexes that might overlap
- Write overhead increase
- Storage impact

For your table:
- Recommended index candidates
- Priority order for creation
- Potential conflicts

Generate index candidates with rationale.

Index Types and When to Use Each

B-Tree Index Applications

B-Tree indexes handle most common indexing scenarios.

Prompt for B-Tree Index Design:

Design B-Tree index strategy for [TABLE/QUERIES]:

B-Tree suitability:

1. **Equality searches**:
   - WHERE status = 'active'
   - WHERE customer_id = 12345
   - B-Tree handles efficiently

2. **Range searches**:
   - WHERE created_at >= '2024-01-01'
   - WHERE price BETWEEN 10 AND 100
   - B-Tree excels here

3. **Prefix matching**:
   - WHERE last_name LIKE 'Smit%'
   - B-Tree supports range scans on prefixes

4. **Sorting**:
   - ORDER BY created_at DESC
   - B-Tree maintains sorted order

B-Tree index design:

1. **Single column**:
   - Column selectivity assessment
   - High-cardinality vs. low-cardinality
   - Null handling

2. **Composite/Composite**:
   - Column order: equality columns first
   - Range column at end
   - Maximum columns (typically 16-32)

3. **Filtered/Partial**:
   - WHERE clause predicate
   - Reduces index size
   - Improves performance for targeted queries

For your queries:
- Recommended B-Tree indexes
- Column ordering rationale
- Partial index conditions if applicable

Generate B-Tree index specifications.

Specialized Index Types

Specialized indexes serve specific access patterns.

Prompt for Specialized Index Selection:

Evaluate specialized indexes for [TABLE/QUERIES]:

**Hash Indexes**:
- Use for: equality joins, lookup tables
- Avoid for: range queries, pattern matching
- PostgreSQL: memory-based, for temporary tables
- MySQL MEMORY: for hash joins

**Bitmap Indexes**:
- Use for: low-cardinality columns (gender, status, region)
- Best for: complex AND/OR combinations
- Avoid for: high-cardinality, frequent updates
- Warehouse: Oracle, PostgreSQL (bitmap), SQL Server

**Full-Text Indexes**:
- Use for: TEXT column searches
- Features: word stemming, relevance ranking
- Implementation: inverted index
- PostgreSQL: GIN or GiST
- MySQL: FULLTEXT
- SQL Server: full-text search

**Spatial Indexes** (if geospatial data):
- Use for: GIS queries, bounding boxes
- Types: R-Tree (PostgreSQL, MySQL Spatial)
- Point-in-polygon, distance queries

**JSON Indexes**:
- Use for: JSONB columns (PostgreSQL)
- GIN indexes for JSONB
- Path-based extraction indexes

For your use case:
- Specialized index recommendations
- Implementation approach
- Performance expectations

Generate specialized index recommendations.

Composite Index Design

Column Ordering

Column order in composite indexes determines which queries benefit.

Prompt for Composite Index Ordering:

Design composite index column order for [TABLE/QUERIES]:

Ordering principles:

1. **Equality columns first**:
   - Columns with WHERE col = value
   - Most selective equality first
   - Reduces index rows to scan

2. **Sort columns next**:
   - Columns in ORDER BY
   - ASC/DESC matches query direction
   - Avoids sort operation

3. **Range columns last**:
   - Columns with >, <, BETWEEN, LIKE
   - Range stops index usage for subsequent columns
   - Only first range column benefits

4. **Covering columns last**:
   - Columns only in SELECT
   - Include for covering index
   - No impact on range scan

Example analysis:

Query: WHERE customer_id = 123 AND created_at > '2024-01-01' ORDER BY amount

Good composite: (customer_id, created_at, amount)
- customer_id equality = first
- created_at range = second
- amount covering = third

Bad composite: (created_at, customer_id, amount)
- Range on first column prevents using rest
- customer_id equality not leveraged

For your queries:
- Analyze column roles (EQ/RANGE/COVER/SORT)
- Recommend column order
- Explain why ordering matters

Generate composite index column ordering analysis.

Covering Index Design

Covering indexes eliminate table access entirely.

Prompt for Covering Index Design:

Design covering index for [QUERY]:

Query to cover:
```sql
[QUERY TEXT]

Current issue:

Table access after index scan
Key lookup performance impact
Explain plan showing [KEY LOOKUP/RID LOOKUP]

Covering index concept:

Add all columns from SELECT to index
Index contains entire query result
No table access needed

Include columns (not keys):

Non-filter, non-join columns from SELECT
PostgreSQL: INCLUDE keyword
SQL Server: INCLUDE keyword
MySQL: all columns already in index

Trade-offs:

Larger index size
More write overhead
Slower on INSERT/UPDATE

For your query:

Recommended covering index
Storage impact estimate
Write overhead impact
Alternatives considered

Generate covering index specification.


## Index Consolidation

### Identifying Redundant Indexes

Multiple indexes may serve overlapping purposes.

**Prompt for Redundancy Analysis:**

Identify redundant indexes for [TABLE]:

Existing indexes:

[INDEX 1]: [COLUMNS] ON [TABLE]
[INDEX 2]: [COLUMNS] ON [TABLE]
…

Redundancy detection:

Duplicate indexes:
- Identical columns in same order
- Remove duplicates immediately
Redundant indexes:
- (A, B) redundant if (A) exists
- (A, B, C) redundant if (A, B) exists
- Partial redundancy: index skip scan
Overlapping indexes:
- (A) and (A, B) overlap
- Query may use either
- Choose based on selectivity
Unused indexes:
- Query Store / Performance counters
- Never used in recent period
- Consider dropping

Analysis approach:

System catalog queries
Query store statistics
Missing index DMVs

For your table:

Redundant indexes identified
Recommended drops
Risk assessment

Generate redundancy analysis with recommendations.


### Index Consolidation Strategy

Consolidation reduces maintenance overhead while preserving performance.

**Prompt for Consolidation Strategy:**

Develop index consolidation for [TABLE]:

Current state:

Index count: [NUMBER]
Total size: [SIZE]
Write frequency: [PER SECOND/HOUR]
Maintenance window: [TIME AVAILABLE]

Consolidation opportunities:

Merge overlapping indexes:
- Combined columns from multiple indexes
- Evaluate composite vs. individual
- Consider column ordering
Convert to covering:
- Add includes to existing index
- Eliminate need for separate index
- Evaluate selectivity impact
Drop unused indexes:
- Monitor for minimum 30-90 days
- Ensure no recent usage
- Document before dropping
Partition indexes:
- Partial indexes for segments
- Horizontal partitioning consideration

Implementation approach:

Phase 1: Drops (lowest risk)
Phase 2: Merges (validate with testing)
Phase 3: New composite (replace multiple)

Validation:

Performance testing
Query plan comparison
Write performance monitoring

Generate consolidation plan with phases.


## Index Maintenance Planning

### Statistics Management

Index effectiveness depends on accurate statistics.

**Prompt for Statistics Management:**

Develop statistics maintenance for [DATABASE/TABLE]:

Statistics importance:

Query optimizer uses statistics
Stale statistics = bad plans
Auto-update thresholds matter

Maintenance approaches:

Auto-update statistics:
- Enable on database (ON by default)
- Threshold: 500 + 20% rows
- Asynchronous vs. synchronous
Manual statistics:
- UPDATE STATISTICS for specific tables
- FULLSCAN vs. sample
- Job scheduling considerations
Filtered statistics:
- Statistics on filtered subsets
- When to use filtered
- Maintenance considerations
Multi-column statistics:
- Statistics on column combinations
- When optimizer needs them
- Creation and maintenance

Schedule recommendations:

Peak hours: minimal
Off-peak: aggressive
Table-specific based on change frequency

For your database:

Current statistics state
Recommended maintenance
Monitoring approach

Generate statistics maintenance plan.


### Index Rebuild Planning

Fragmentation degrades index performance over time.

**Prompt for Rebuild Planning:**

Plan index rebuilds for [DATABASE/TABLES]:

Fragmentation assessment:

Fragmentation levels:
- 0-10%: Acceptable
- 10-30%: Consider reorganize
- 30%+: Rebuild recommended
Detection queries:
- sys.dm_db_index_physical_stats (SQL Server)
- pg_stat_user_indexes (PostgreSQL)
- ANALYZE TABLE (MySQL)

Reorganize vs. Rebuild:

REORGANIZE (ALTER INDEX REORGANIZE):
- Online operation
- Less resource intensive
- Slower for heavily fragmented
- No statistics update
REBUILD (ALTER INDEX REBUILD):
- Usually offline (SQL Server)
- Can be online (Enterprise edition)
- Updates statistics
- Better for high fragmentation

Maintenance scheduling:

During maintenance window
Consider LOB and XML indexes
Clustered index includes all data

For your tables:

Fragmentation assessment
Recommended operation
Schedule timing

Generate rebuild plan with prioritization.


## Performance Troubleshooting

### Slow Query Analysis

Diagnose why specific queries are slow.

**Prompt for Slow Query Analysis:**

Analyze slow query for [QUERY]:

Query text:

[QUERY TEXT]

Execution context:

Frequency: [HOW OFTEN RUNS]
Peak time: [WHEN SLOW]
Expected runtime: [WHAT IS ACCEPTABLE]

Explain plan analysis:

Full table scans detected?
Index scans vs. seeks
Nested loop vs. hash joins
Sort operations
Missing indexes

Wait analysis:

Common wait types
I/O waits
Lock waits
Network waits

Diagnostic queries:

-- SQL Server
SELECT * FROM sys.dm_exec_query_stats
WHERE sql_handle = ...

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) [QUERY];

Root causes and solutions:

Missing index -> Create
Outdated statistics -> Update
Bad plan -> Hints or restructure
Lock contention -> Isolation level or partitioning

Generate diagnosis with specific recommendations.


### Missing Index Detection

Identify indexes that would improve performance.

**Prompt for Missing Index Analysis:**

Detect missing indexes for [DATABASE/WORKLOAD]:

Detection methods:

Query Store (SQL Server):

SELECT qs.query_id, qs.sql_handle,
       migs.user_seeks * avg_total_user_cost * (avg_user_impact / 100.0)
       AS index_advantage,
       migs.user_seeks, migs.user_scans
FROM sys.query_store_query_text qt
CROSS APPLY sys.dm_db_query_stats(...)
WHERE ...

PostgreSQL pg_stat_statements:
- Track query frequencies
- Identify high-cost queries
- Correlate with table access
MySQL Performance Schema:
- Statement analysis
- Table access monitoring
Manual analysis:
- Review explain plans
- Identify table scans
- Design index for WHERE/JOIN

Candidate evaluation:

Benefit: user_seeks * avg_total_user_cost
Cost: index size, maintenance overhead
Impact: avg_user_impact percentage

For your workload:

Missing index candidates
Priority ranking
Implementation order

Generate missing index report with prioritization.


## Cloud and Modern Database Considerations

### Cloud Database Indexing

Cloud databases handle indexing differently than on-premises.

**Prompt for Cloud Indexing:**

Optimize indexing for [CLOUD DATABASE SERVICE]:

Service-specific features:

Amazon RDS/Aurora:
- Auto scaling storage
- Performance Insights for analysis
- Read replicas for read scaling
- Index recommendations via Performance Insights
Azure SQL Database:
- Automatic tuning
- Intelligent Performance features
- Auto-index management
- Query Store enabled by default
Google Cloud SQL/Cloud Spanner:
- Automatic index recommendations
- Spanner: strong consistency limits
- Read replicas considerations

Cloud-specific strategies:

Use cloud-native monitoring
Leverage automatic tuning cautiously
Consider read replica routing
Evaluate auto-scaling storage costs

Managed index services:

When to use
Limitations
Cost implications

For your cloud environment:

Cloud-specific recommendations
Features to leverage
Pitfalls to avoid

Generate cloud indexing strategy.


### Modern Workload Considerations

Modern applications have different indexing needs.

**Prompt for Modern Workload Indexing:**

Address modern workload indexing for [APPLICATION]:

Modern workload patterns:

Microservices:
- Smaller databases per service
- Service-specific access patterns
- Index for actual queries, not theoretical
HTAP workloads (hybrid transactional/analytical):
- Row vs. columnar storage
- Operational vs. analytical indexes
- In-memory considerations
Real-time analytics:
- Sub-second response requirements
- Materialized views
- Pre-aggregation
Time-series data:
- Time-based partitioning
- Time-ordered insert patterns
- Downsampling/aggregation indexes
JSON/JSONB data:
- GIN indexes for JSONB (PostgreSQL)
- Document store patterns
- Path-based indexing

For your workload:

Workload classification
Index strategy alignment
Modern features to leverage

Generate modern workload indexing recommendations.


## FAQ: Database Indexing Excellence

### How many indexes should a table have?

There is no universal limit, but each index adds write overhead and storage cost. Tables with heavy INSERT/UPDATE workloads (like transaction logs) should have fewer indexes. Tables used primarily for reads (like reporting tables) can have more. Monitor write latency and table size as guides. If you have more than 5-6 indexes on a frequently written table, evaluate consolidation.

### Should we index every column used in WHERE clauses?

No. Indexes benefit high-selectivity queries (returning few rows). Indexing low-selectivity columns (like gender, status flags) rarely helps unless combined with high-selectivity columns in a composite index. Always evaluate the selectivity of your WHERE clause combinations before creating indexes.

### How do indexes affect transaction log growth?

Every index update generates transaction log entries. More indexes means more log volume, which affects backup/recovery times and storage costs. For high-volume write tables, limiting indexes directly reduces log growth. Consider whether all indexes are necessary for your write workload.

### When should we use filtered/partial indexes?

Use filtered indexes when you frequently query a subset of rows (like active records, recent data, or specific categories). Partial indexes are smaller, faster to maintain, and more likely to be used by the optimizer for matching queries. They work well for timestamp-based queries on tables with mixed old and new data.

### How do we know if an index is being used?

Query your database's index usage statistics: sys.dm_db_index_usage_stats (SQL Server), pg_stat_user_indexes (PostgreSQL), or Performance Schema (MySQL). Look for user_seeks, user_scans, and user_lookups. Indexes with high user_seeks are being used for seeks. Indexes with only user_scans may be overkill. Indexes with zero usage for extended periods are candidates for removal.

## Conclusion

Database indexing is part science, part art. The science provides frameworks for understanding index mechanics and optimization strategies. The art comes from knowing your workload intimately—understanding which queries matter, how data grows, and how users access the system.

The AI prompts in this guide help DBAs systematically analyze, design, and maintain indexes that genuinely improve performance rather than adding complexity without benefit.

The key takeaways from this guide are:

1. **Match index type to access pattern** - B-Tree for ranges, hash for equality, specialized types for special data.

2. **Column order in composite indexes matters enormously** - Equality first, range last, covering columns at the end.

3. **Consolidation prevents index sprawl** - Regular reviews identify redundant and unused indexes.

4. **Statistics are as important as indexes** - The optimizer cannot use indexes effectively with stale statistics.

5. **Monitor in production, not just in development** - Query patterns and data volumes change; indexes that helped once can hurt later.

Your next step is to run the missing index detection query against your most critical tables and create a prioritized index improvement plan. AI Unpacker provides the framework; your database expertise provides the judgment.

Database Indexing Strategy AI Prompts for DBAs