- Home
- Skills
- Data & Databases
- SQL query writer optimizer
Works with the AI Agents you already use
SQL query writer optimizer
Convert business requirements into optimized MySQL/PostgreSQL queries with EXPLAIN analysis and index recommendations.
$6
SQL query writer optimizer
Example session with this skill installed
I have a MySQL 8.0 query: SELECT * FROM orders WHERE status = 'active' ORDER BY created_at DESC LIMIT 100000, 10. The orders table has 50 million rows. How do I optimize this?
- Read your context and instructions
- Compiled the sql query writer
I have analyzed the deep pagination issue in your MySQL 8.0 query. Scanning 100,010 rows to return 10 is inefficient because it triggers a heap fetch for every discarded row.
Root cause
The query uses a high OFFSET, forcing the engine to read and then discard 100,000 rows. Since you are using SELECT *, MySQL cannot use a covering index and must perform a "bookmark lookup" (heap fetch) for all columns for every row scanned.
Optimized SQL (Deferred Join)
SELECT t1.*
FROM orders AS t1
INNER JOIN (
SELECT id
FROM orders
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100000, 10
) AS t2 ON t1.id = t2.id
ORDER BY t1.created_at DESC;
Original SQL vs Optimized SQL
| Item | Original SQL | Optimized SQL |
|---|---|---|
| Strategy | Full Row Scan | Deferred Join |
| Index Usage | Likely status or created_at | Covering Index in Subquery |
| Heap Fetches | 100,010 | 10 |
| Filesort | High Risk | Minimized |
Index recommendations
CREATE INDEX idx_status_created_at_id ON orders (status, created_at, id);
Rationale: This composite index follows the equality-first rule (status) followed by the sort column (created_at). Including the ID makes this a covering index for the subquery, allowing the offset scan to happen entirely in memory without touching the data pages.
Scan-reduction analysis
By using a deferred join, the engine only fetches the full row data for the 10 rows actually returned. While the index still scans 100,000 entries, you reduce I/O operations by approximately 99.99%.
Next steps
- Run
EXPLAINon the optimized version to verify "Using index" appears in the subquery's Extra field. - If this is for a "Load More" UI, consider switching to keyset pagination (e.g.,
WHERE created_at < ?). - Verify that
idis the primary key for the join.
Connects securely to your tools. The creator never sees your data.
About this skill
Writing performant SQL requires more than just correct syntax. It requires an understanding of how the database engine actually retrieves data. This skill bridges the gap between business requirements and optimized, dialect-aware SQL for MySQL and PostgreSQL.
What it does
- SQL Authoring: Generates executable SELECT, JOIN, CTE, and window function queries tailored for MySQL 5.7/8.0 or PostgreSQL 12-16.
- EXPLAIN Interpretation: Analyzes execution plans line-by-line to identify full table scans, filesorts, and temporary table bottlenecks.
- Index Design: Recommends B+tree index structures, covering indexes, and composite index ordering based on leftmost-prefix matching.
- Query Refactoring: Rewrites slow queries to eliminate anti-patterns like non-sargable functions, implicit type casts, and deep-offset pagination.
- Scan-Reduction Analysis: Provides original-vs-optimized comparisons with estimated reductions in rows scanned.
How it works
- Provide your business requirements, schema details (table/column names), and approximate data scale.
- Specify your database engine and version to ensure dialect-specific syntax and optimizer behavior.
- Paste existing slow queries or EXPLAIN output for a detailed performance audit.
- Receive optimized SQL, index DDL, and a breakdown of why the suggested changes improve performance.
Frameworks & tools
This skill is built on methodologies from the MySQL 8.0 Reference Manual, PostgreSQL Documentation, and industry-standard texts like High Performance MySQL and SQL Performance Explained.
Why this beats prompting it yourself
Generic LLMs often mix dialects or miss the nuance of how implicit type conversion kills indexes. This skill uses a structured knowledge system to verify key_len calculations and check for NULL-semantics risks in subqueries that generic prompts overlook.
Use cases
- Converting complex business logic into efficient window functions or CTEs.
- Troubleshooting a production query that has suddenly slowed down.
- Designing the optimal composite index for a new reporting dashboard.
- Refactoring legacy SQL to remove
SELECT *and deep pagination issues.
Known limitations
Does not connect to live databases or execute SQL. It cannot inspect real execution plans unless you provide the EXPLAIN output text manually. It does not support NoSQL or data-modeling design.
Does not connect to live databases. Requires manual EXPLAIN output for analysis. Supports only MySQL and PostgreSQL. Cannot perform data-modeling/ERD design.
How to use
- 1
Connect your agent to the Agensi MCP
Copy the prompt and paste it into Claude, ChatGPT, Cursor or any agent that supports MCP. It sets up the connection for you. Manual setup
- 2
Sign in to Agensi
Your agent shows a sign-in link. Sign in and click Allow.
- 3
Ask for what you need
Your agent loads this skill and uses it on your task. It always asks before any payment.
Reviews
No reviews yet
Be one of the first to try it. Every listed skill goes through the trust checks below.
Security scanned
Checked by our 8-point scan before listing
Fresh listing
Recently published to Agensi
Money-back guarantee
Doesn't do what its page promises? You get your money back.
Trust & safety
Security scan
Security scan passed today
Permissions
No special permissions declared or detected