How do I use the "Create Optimized Database Indexes" prompt?

Copy the prompt template, fill in the variables (DATABASE_TYPE, TABLE_STRUCTURE, COMMON_QUERIES, PERFORMANCE_ISSUES, DATA_VOLUME, READ_WRITE_RATIO), and paste it into ChatGPT, Claude, or Gemini.

What AI tools work with this coding prompt?

This prompt works with chatgpt, claude, gemini. Simply copy and paste the template into any of these AI assistants.

Tips for getting the best results?

Always test indexes on a staging environment with realistic data volumes before production deployment Use EXPLAIN ANALYZE to measure actual performance improvements, not just execution plans Consider partial indexes for queries with frequent WHERE conditions to reduce storage overhead Monitor index usage statistics regularly to identify unused indexes that should be dropped Implement indexes during low-traffic periods and use CONCURRENTLY option when available

coding intermediate

Create Optimized Database Indexes

Generate efficient database indexes with AI. Optimize query performance, reduce response time, and improve database efficiency instantly.

Works with: chatgptclaudegemini

Prompt Template

You are a senior database administrator with expertise in query optimization and index design. I need you to create optimal database indexes for my [DATABASE_TYPE] database. Here's my situation: - Database type: [DATABASE_TYPE] - Table structure: [TABLE_STRUCTURE] - Common queries: [COMMON_QUERIES] - Current performance issues: [PERFORMANCE_ISSUES] - Expected data volume: [DATA_VOLUME] - Read/write ratio: [READ_WRITE_RATIO] Please analyze this information and provide: 1. **Recommended Indexes**: Specific CREATE INDEX statements with clear naming conventions 2. **Index Strategy**: Explain the reasoning behind each index (covering queries, cardinality considerations, etc.) 3. **Performance Impact**: Expected query performance improvements 4. **Maintenance Considerations**: Storage overhead, update impact, and maintenance requirements 5. **Alternative Options**: Composite vs single-column indexes, partial indexes if applicable 6. **Implementation Order**: Prioritized sequence for implementing indexes 7. **Monitoring Recommendations**: How to track index effectiveness after implementation Format your response with clear SQL statements and practical explanations that a development team can implement immediately.

Variables to Customize

[DATABASE_TYPE]

The database management system being used

Example: PostgreSQL

[TABLE_STRUCTURE]

Description of table schemas, columns, and data types

Example: Users table: id (primary key), email (varchar), created_at (timestamp), status (enum), department_id (foreign key)

[COMMON_QUERIES]

Frequently executed queries that need optimization

Example: SELECT * FROM users WHERE email = ? AND status = 'active'; SELECT COUNT(*) FROM users WHERE created_at > ? GROUP BY department_id

[PERFORMANCE_ISSUES]

Current performance problems being experienced

Example: Login queries taking 2-3 seconds, user search by email timing out, dashboard reports running slowly

[DATA_VOLUME]

Current and expected data size

Example: 500K users currently, growing by 10K monthly

[READ_WRITE_RATIO]

Proportion of read vs write operations

Example: 80% reads, 20% writes

Example Output

## Recommended Indexes for PostgreSQL Users Table ### 1. Primary Indexes ```sql CREATE INDEX CONCURRENTLY idx_users_email_active ON users (email) WHERE status = 'active'; CREATE INDEX CONCURRENTLY idx_users_created_dept ON users (created_at, department_id); ``` ### Index Strategy **Email Login Index**: The partial index on email with status filter covers your login queries efficiently, reducing index size by ~60% since it excludes inactive users. **Time-Department Composite**: Supports both date-range queries and department grouping in a single index, eliminating the need for separate indexes. ### Performance Impact - Login queries: Expected reduction from 2-3s to <50ms - Email searches: 95% performance improvement - Dashboard reports: 70% faster execution ### Implementation Order 1. Deploy email index first (addresses critical login performance) 2. Add composite index during low-traffic hours 3. Monitor query plans for 48 hours before additional changes ### Maintenance Considerations - Storage overhead: ~15MB additional space - Write performance impact: <5% due to high read ratio - Recommend REINDEX monthly for optimal performance

Pro Tips for Best Results

Always test indexes on a staging environment with realistic data volumes before production deployment
Use EXPLAIN ANALYZE to measure actual performance improvements, not just execution plans
Consider partial indexes for queries with frequent WHERE conditions to reduce storage overhead
Monitor index usage statistics regularly to identify unused indexes that should be dropped
Implement indexes during low-traffic periods and use CONCURRENTLY option when available