Prompt lesson · 19 prompts
Indexing Strategies prompts for Database Administrators
19 ready-to-use prompts from our AI for Database Administrators course. Copy one, fill in the {{placeholders}}, and paste it into ChatGPT, Claude, Gemini or any other AI.
Analyze Index Statistics
Use this when you need to gather and analyze index statistics to optimize database performance.
Role You are a database performance expert specializing in index optimization. Your goal is to help me understand and improve index usage through statistical analysis.
Context you provide
- {{database_table}}: The specific table you want to analyze (e.g.,
orders). - {{dbms}}: The database management system in use (e.g., PostgreSQL, MySQL).
- {{performance_goal}}: The primary objective, such as reducing query latency or improving write throughput.
Instructions
- Ask for any missing context before starting.
- Explain how to gather index statistics for the given table in the specified DBMS, including relevant commands or queries.
- Describe how to interpret the statistics to identify underutilized or overutilized indexes.
- Provide best practices for maintaining accurate statistics, including update frequency and methods.
- Suggest a practical approach to adjust indexing strategy based on the analysis.
Output format Provide a structured response with sections for gathering, analyzing, and acting on statistics. Use bullet points and include concrete examples. Keep the tone technical but accessible.
Guardrails
- Do not invent specific metrics or commands without verifying they apply to the stated DBMS.
- Flag any assumptions about the database size or workload.
- Stay focused on index statistics, not broader database tuning.
Example
- {{database_table}}:
orders; {{dbms}}: PostgreSQL; {{performance_goal}}: reduce query time for date-range reports.
Open this prompt Analysis · Intermediate
Clustered Index Implementation
Use this when you need to understand, implement, or optimize clustered indexes for better database performance.
Role You are a database performance expert specializing in indexing strategies. Your goal is to explain clustered indexing clearly and provide actionable implementation guidance.
Context you provide
- {{database_type}}: The database system (e.g., SQL Server, PostgreSQL, MySQL).
- {{table_name}}: The specific table you're working with.
- {{query_patterns}}: The frequent queries or access patterns you need to optimize.
Instructions
- Ask for missing context before proceeding.
- Explain how clustered indexes physically order data and impact query performance.
- Provide a step-by-step guide for implementing a clustered index on the {{table_name}} in {{database_type}}.
- Discuss trade-offs, such as insert/update overhead and storage considerations.
- Suggest best practices for selecting columns based on {{query_patterns}}.
Output format Provide a structured explanation with headings: overview, implementation steps, trade-offs, and best practices. Include code examples where relevant. Keep the response 400-600 words, technical but accessible.
Guardrails
- Do not provide database-specific syntax unless the system is known.
- Flag assumptions about the table structure or workload.
- Avoid recommending clustered indexes without considering maintenance costs.
Example Database type: SQL Server; table name: Orders; query patterns: frequent range queries on OrderDate.
Open this prompt Learning · Intermediate
Create Optimal Indexes
Use this when you need to design and create indexes that improve query performance based on your database schema and workload.
Open this prompt Creating · Intermediate
Design Covering Indexes
Use this when you need to optimize query performance by creating covering indexes that include all required columns.
Open this prompt Analysis · Intermediate
Implement Filtered Indexes
Use this when you need to optimize queries that target a subset of rows in a large table using filtered indexes.
Open this prompt Creating · Intermediate
Index Large Tables Effectively
Use this when you need to optimize indexing and partitioning for large tables with millions of records.
Open this prompt Planning · Advanced
Index Maintenance Strategy
Use this when you need to plan and execute index maintenance to keep database performance optimal.
Open this prompt Planning · Intermediate
Index Optimization Recommendations
Use this when you need to refine existing indexes to boost query speed and overall database efficiency.
Open this prompt Analysis · Advanced
Index Partitioning Guidance
Use this when you need to partition indexes to improve performance and manage large datasets efficiently.
Open this prompt Planning · Advanced
Index Performance Monitoring
Use this when you need to track index usage and identify performance bottlenecks in your database.
Open this prompt Analysis · Intermediate
Index Selection for Query Performance
Use this when you need to choose the right indexes for your tables based on query patterns and performance goals.
Open this prompt Analysis · Intermediate
Indexing Strategies by DBMS
Use this when you need tailored indexing advice for a specific database system like MySQL, Oracle, or SQL Server.
Open this prompt Research · Intermediate
Indexing Text and Binary Data
Use this when you need to implement or optimize full-text and binary indexing for efficient search and retrieval in a database.
Open this prompt Research · Advanced
Manage Index Fragmentation Effectively
Use this when you need to monitor, diagnose, and resolve index fragmentation to optimize database performance.
Open this prompt Planning · Intermediate
Optimize Foreign Key Indexing
Use this when you need to improve join performance and maintain referential integrity by indexing foreign key columns.
Open this prompt Planning · Intermediate
Optimize Index Compression
Use this when you need to reduce storage costs and improve query performance by compressing database indexes.
Open this prompt Analysis · Advanced
Optimize Spatial Data Indexing
Use this when you need to improve query performance for geographical or spatial data using specialized indexing methods.
Open this prompt Research · Advanced
Optimizing Non-Clustered Indexes
Use this when you need to design or refine non-clustered indexes to speed up data retrieval on frequently queried columns.
Open this prompt Research · Intermediate
Resolve Index Fragmentation
Use this when you need to identify and fix index fragmentation to maintain database performance.
Open this prompt Analysis · Intermediate