SQL Query Optimization for High-Volume Tables

As databases grow, minor inefficiencies become major bottlenecks. This prompt helps you apply senior-level database optimization techniques to your existing queries.

The Core Prompt

Optimize the following SQL query for better performance on a large "orders" table with millions of rows: SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2025-01-01' ORDER BY created_at DESC LIMIT 100;. Suggest necessary indexes.

Optimization Logic

The AI will analyze the "WHERE" and "ORDER BY" clauses to suggest composite indexes, which are the most effective way to speed up complex queries on large datasets.

Usage Tips

• Explain Plan: Ask the AI to "explain how the database engine would execute this query with and without the index."
• Specific DB: Mention if you are using PostgreSQL, MySQL, or SQL Server, as optimization strategies vary.

Example AI Output

Suggestion: Create a composite index on (status, created_at). This allows the database to filter and sort in a single operation without a full table scan.