{"id":"d1eebfd1-5068-4490-a113-e717ac36c5e7","shortId":"BEcJmR","kind":"skill","title":"SQL Query Optimizer","tagline":"Reviews SQL queries for performance issues and rewrites them with optimized execution plans.","description":"# SQL Query Optimizer\n\n## What this skill does\n\nThis skill directs the agent to analyze a SQL query for common performance anti-patterns and rewrite it to execute more efficiently. It checks indexes, join strategies, subquery usage, N+1 patterns, unnecessary full table scans, and more — then provides a side-by-side comparison of the original and optimized query with a plain-English explanation of each change.\n\nUse this when a query is running slowly in production, when you're writing a complex query for the first time and want a second opinion, or when you're reviewing a pull request that touches database queries.\n\n## How to use\n\n### Claude Code / Cursor / Codex\n\nCopy this file to `.agents/skills/sql-query-optimizer/SKILL.md` in your project root (for Claude Code), or add the instructions below to your `.cursorrules` (for Cursor).\n\nThen ask:\n- *\"Optimize this query using the SQL Query Optimizer skill.\"*\n- *\"This query takes 3 seconds on 500k rows. Use the SQL Query Optimizer skill to improve it.\"*\n\nProvide the query, and optionally:\n- The database system (PostgreSQL, MySQL, SQLite, etc.)\n- Relevant table schemas and row counts\n- Any existing index definitions\n- The `EXPLAIN` or `EXPLAIN ANALYZE` output if available\n\n## The Prompt / Instructions for the Agent\n\nWhen asked to optimize a SQL query, follow these steps:\n\n1. **Identify the database system** (PostgreSQL, MySQL, SQLite, MSSQL). Syntax and optimizer behavior differ. If not specified, ask.\n\n2. **Analyze for these anti-patterns in order:**\n\n   - **Missing indexes:** Are columns in `WHERE`, `JOIN ON`, `ORDER BY`, or `GROUP BY` clauses indexed? Flag unindexed columns on large tables.\n   - **SELECT *:** Does the query select all columns when only a few are needed? Unnecessary columns increase I/O and memory.\n   - **N+1 queries:** Is this query run inside a loop in application code (indicated by a comment or context)? Suggest a single JOIN or subquery instead.\n   - **Correlated subqueries:** Does the `WHERE` clause contain a subquery that references the outer query? These execute once per row. Rewrite as a JOIN or CTE.\n   - **Non-sargable predicates:** Are functions applied to indexed columns in `WHERE` (e.g., `WHERE YEAR(created_at) = 2024`)? This prevents index use.\n   - **Implicit type coercions:** Do joins compare columns of different types (e.g., `INT` vs `VARCHAR`)? This forces a full scan.\n   - **Unnecessary DISTINCT or ORDER BY:** Is `DISTINCT` used where it's not needed? Is `ORDER BY` applied to a subquery that is later filtered?\n   - **Large offset pagination:** Does the query use `LIMIT x OFFSET y` with a large offset? Suggest keyset pagination instead.\n\n3. **Write the optimized query.** Use CTEs (`WITH` clauses) to improve readability when the logic is complex.\n\n4. **List index recommendations** separately. Format as `CREATE INDEX` statements that can be run directly.\n\n5. **Output format:**\n\n```sql\n-- ORIGINAL QUERY\n[paste original here]\n\n-- OPTIMIZED QUERY\n[rewritten query here]\n```\n\n```markdown\n### What changed and why\n\n- **Change 1:** [Explanation]\n- **Change 2:** [Explanation]\n\n### Recommended indexes\n\n\\```sql\nCREATE INDEX idx_orders_user_id ON orders(user_id);\n\\```\n\n### Estimated impact\n\n[Brief note on expected improvement — e.g., \"This should reduce the query from a full table scan to an index seek, likely cutting execution time by 80-95% on large tables.\"]\n```\n\n6. If the query cannot be improved, say so explicitly and explain why.\n\n## Example\n\n**Input to Agent:**\n> \"Optimize this PostgreSQL query. The `orders` table has 2M rows. `user_id` is not indexed.\n>\n> ```sql\n> SELECT *\n> FROM orders\n> WHERE user_id = 42\n>   AND status = 'pending'\n>   AND EXTRACT(YEAR FROM created_at) = 2025\n> ORDER BY created_at DESC;\n> ```\"\n\n**Output from Agent:**\n\n```sql\n-- ORIGINAL QUERY\nSELECT *\nFROM orders\nWHERE user_id = 42\n  AND status = 'pending'\n  AND EXTRACT(YEAR FROM created_at) = 2025\nORDER BY created_at DESC;\n\n-- OPTIMIZED QUERY\nSELECT id, user_id, status, amount, created_at\nFROM orders\nWHERE user_id = 42\n  AND status = 'pending'\n  AND created_at >= '2025-01-01'\n  AND created_at < '2026-01-01'\nORDER BY created_at DESC;\n```\n\n**What changed and why**\n\n- **Removed SELECT *:** Selected only the columns likely needed. Reduces I/O and memory overhead.\n- **Replaced EXTRACT(YEAR FROM created_at):** The function call made the query non-sargable (the index on `created_at` could not be used). Replacing it with a range predicate allows an index seek.\n- **user_id filter first:** With the index below, PostgreSQL will use `user_id` to narrow rows before applying the date range filter.\n\n**Recommended indexes**\n```sql\nCREATE INDEX idx_orders_user_id_status_created ON orders(user_id, status, created_at DESC);\n```\n\n**Estimated impact**\n\nThis changes a full sequential scan of 2M rows to an index scan targeting only rows for `user_id = 42`. On typical data distributions, this should reduce query time from seconds to milliseconds.\n\n## Notes\n\n- Always provide the database system. PostgreSQL's planner differs significantly from MySQL's.\n- If you have the output of `EXPLAIN ANALYZE`, paste it — it gives the agent exact scan types and row estimates to work with.\n- The skill focuses on read query optimization. For write-heavy tables, index recommendations should be weighed against insert/update overhead.\n- The skill does not run queries or access your database — all analysis is based on the query text and schema information you provide.","tags":["sql","query","optimizer","openagentskills","notysoty","agent-skills","claude","claude-code","claude-skills","cline","cursor","llm"],"capabilities":["skill","source-notysoty","skill-sql-query-optimizer","topic-agent-skills","topic-claude","topic-claude-code","topic-claude-skills","topic-cline","topic-cursor","topic-llm","topic-llm-skills","topic-skills"],"categories":["openagentskills"],"synonyms":[],"warnings":[],"endpointUrl":"https://skills.sh/Notysoty/openagentskills/sql-query-optimizer","protocol":"skill","transport":"skills-sh","auth":{"type":"none","details":{"cli":"npx skills add Notysoty/openagentskills","source_repo":"https://github.com/Notysoty/openagentskills","install_from":"skills.sh"}},"qualityScore":"0.454","qualityRationale":"deterministic score 0.45 from registry signals: · indexed on github topic:agent-skills · 8 github stars · SKILL.md body (5,468 chars)","verified":false,"liveness":"unknown","lastLivenessCheck":null,"agentReviews":{"count":0,"score_avg":null,"cost_usd_avg":null,"success_rate":null,"latency_p50_ms":null,"narrative_summary":null,"summary_updated_at":null},"enrichmentModel":"deterministic:skill-github:v1","enrichmentVersion":1,"enrichedAt":"2026-05-18T19:13:24.844Z","embedding":null,"createdAt":"2026-05-18T13:20:46.782Z","updatedAt":"2026-05-18T19:13:24.844Z","lastSeenAt":"2026-05-18T19:13:24.844Z","tsv":"'+1':55,295 '-01':637,638,643,644 '-95':527 '1':227,481 '2':245,484 '2024':362 '2025':580,608,636 '2026':642 '2m':556,751 '3':167,429 '4':446 '42':570,598,629,763 '5':461 '500k':170 '6':531 '80':526 'access':841 'add':144 'agent':28,216,547,588,804 'agents/skills/sql-query-optimizer/skill.md':135 'allow':697 'alway':778 'amount':621 'analysi':845 'analyz':30,207,246,798 'anti':38,250 'anti-pattern':37,249 'appli':351,402,718 'applic':305 'ask':154,218,244 'avail':210 'base':847 'behavior':239 'brief':501 'call':675 'cannot':535 'chang':85,477,480,483,651,745 'check':48 'claud':127,141 'claus':267,325,437 'code':128,142,306 'codex':130 'coercion':369 'column':257,271,281,289,354,373,659 'comment':310 'common':35 'compar':372 'comparison':70 'complex':101,445 'contain':326 'context':312 'copi':131 'correl':320 'could':687 'count':198 'creat':360,453,489,578,583,606,611,622,634,640,647,671,685,726,733,739 'cte':344 'ctes':435 'cursor':129,152 'cursorrul':150 'cut':522 'data':766 'databas':122,187,230,781,843 'date':720 'definit':202 'desc':585,613,649,741 'differ':240,375,786 'direct':26,460 'distinct':387,392 'distribut':767 'e.g':357,377,506 'effici':46 'english':81 'estim':499,742,810 'etc':192 'exact':805 'exampl':544 'execut':15,44,335,523 'exist':200 'expect':504 'explain':204,206,542,797 'explan':82,482,485 'explicit':540 'extract':575,603,668 'file':133 'filter':409,703,722 'first':105,704 'flag':269 'focus':816 'follow':224 'forc':382 'format':451,463 'full':58,384,514,747 'function':350,674 'give':802 'group':265 'heavi':824 'i/o':291,663 'id':494,498,559,569,597,617,619,628,702,713,731,737,762 'identifi':228 'idx':491,728 'impact':500,743 'implicit':367 'improv':179,439,505,537 'increas':290 'index':49,201,255,268,353,365,448,454,487,490,519,562,683,699,707,724,727,755,826 'indic':307 'inform':854 'input':545 'insert/update':832 'insid':301 'instead':319,428 'instruct':146,213 'int':378 'issu':9 'join':50,260,316,342,371 'keyset':426 'larg':273,410,423,529 'later':408 'like':521,660 'limit':417 'list':447 'logic':443 'loop':303 'made':676 'markdown':475 'memori':293,665 'millisecond':776 'miss':254 'mssql':235 'mysql':190,233,789 'n':54,294 'narrow':715 'need':287,398,661 'non':346,680 'non-sarg':345,679 'note':502,777 'offset':411,419,424 'opinion':111 'optim':3,14,19,75,155,162,176,220,238,432,470,548,614,820 'option':185 'order':253,262,389,400,492,496,553,566,581,594,609,625,645,729,735 'origin':73,465,468,590 'outer':332 'output':208,462,586,795 'overhead':666,833 'pagin':412,427 'past':467,799 'pattern':39,56,251 'pend':573,601,632 'per':337 'perform':8,36 'plain':80 'plain-english':79 'plan':16 'planner':785 'postgresql':189,232,550,709,783 'predic':348,696 'prevent':364 'product':95 'project':138 'prompt':212 'provid':64,181,779,856 'pull':118 'queri':2,6,18,33,76,90,102,123,157,161,165,175,183,223,278,296,299,333,415,433,466,471,473,511,534,551,591,615,678,771,819,839,850 'rang':695,721 're':98,115 'read':818 'readabl':440 'recommend':449,486,723,827 'reduc':509,662,770 'refer':330 'relev':193 'remov':654 'replac':667,691 'request':119 'review':4,116 'rewrit':11,41,339 'rewritten':472 'root':139 'row':171,197,338,557,716,752,759,809 'run':92,300,459,838 'sargabl':347,681 'say':538 'scan':60,385,516,749,756,806 'schema':195,853 'second':110,168,774 'seek':520,700 'select':275,279,564,592,616,655,656 'separ':450 'sequenti':748 'side':67,69 'side-by-sid':66 'signific':787 'singl':315 'skill':22,25,163,177,815,835 'skill-sql-query-optimizer' 'slowli':93 'source-notysoty' 'specifi':243 'sql':1,5,17,32,160,174,222,464,488,563,589,725 'sqlite':191,234 'statement':455 'status':572,600,620,631,732,738 'step':226 'strategi':51 'subqueri':52,318,321,328,405 'suggest':313,425 'syntax':236 'system':188,231,782 'tabl':59,194,274,515,530,554,825 'take':166 'target':757 'text':851 'time':106,524,772 'topic-agent-skills' 'topic-claude' 'topic-claude-code' 'topic-claude-skills' 'topic-cline' 'topic-cursor' 'topic-llm' 'topic-llm-skills' 'topic-skills' 'touch':121 'type':368,376,807 'typic':765 'unindex':270 'unnecessari':57,288,386 'usag':53 'use':86,126,158,172,366,393,416,434,690,711 'user':493,497,558,568,596,618,627,701,712,730,736,761 'varchar':380 'vs':379 'want':108 'weigh':830 'work':812 'write':99,430,823 'write-heavi':822 'x':418 'y':420 'year':359,576,604,669","prices":[{"id":"be943dcc-b02c-4a91-9d81-93bc9b146c13","listingId":"d1eebfd1-5068-4490-a113-e717ac36c5e7","amountUsd":"0","unit":"free","nativeCurrency":null,"nativeAmount":null,"chain":null,"payTo":null,"paymentMethod":"skill-free","isPrimary":true,"details":{"org":"Notysoty","category":"openagentskills","install_from":"skills.sh"},"createdAt":"2026-05-18T13:20:46.782Z"}],"sources":[{"listingId":"d1eebfd1-5068-4490-a113-e717ac36c5e7","source":"github","sourceId":"Notysoty/openagentskills/sql-query-optimizer","sourceUrl":"https://github.com/Notysoty/openagentskills/tree/main/skills/sql-query-optimizer","isPrimary":false,"firstSeenAt":"2026-05-18T13:20:46.782Z","lastSeenAt":"2026-05-18T19:13:24.844Z"}],"details":{"listingId":"d1eebfd1-5068-4490-a113-e717ac36c5e7","quickStartSnippet":null,"exampleRequest":null,"exampleResponse":null,"schema":null,"openapiUrl":null,"agentsTxtUrl":null,"citations":[],"useCases":[],"bestFor":[],"notFor":[],"kindDetails":{"org":"Notysoty","slug":"sql-query-optimizer","github":{"repo":"Notysoty/openagentskills","stars":8,"topics":["agent-skills","claude","claude-code","claude-skills","cline","cursor","llm","llm-skills","skills"],"license":"mit","html_url":"https://github.com/Notysoty/openagentskills","pushed_at":"2026-03-28T06:50:19Z","description":"A  community-driven library of reusable AI agent skills for Claude Code, Cursor, Codex, Cline, and more.","skill_md_sha":"c66ca753f79aa7818a8945f07eb62b04bb855d40","skill_md_path":"skills/sql-query-optimizer/SKILL.md","default_branch":"main","skill_tree_url":"https://github.com/Notysoty/openagentskills/tree/main/skills/sql-query-optimizer"},"layout":"multi","source":"github","category":"openagentskills","frontmatter":{"name":"SQL Query Optimizer","description":"Reviews SQL queries for performance issues and rewrites them with optimized execution plans."},"skills_sh_url":"https://skills.sh/Notysoty/openagentskills/sql-query-optimizer"},"updatedAt":"2026-05-18T19:13:24.844Z"}}