{"id":"414b2cf6-ae15-4482-8f3f-919b3c8abc13","shortId":"QYes6Z","kind":"skill","title":"Sql Optimization","tagline":"Awesome Copilot skill by Github","description":"# SQL Performance Optimization Assistant\n\nExpert SQL performance optimization for ${selection} (or entire project if no selection). Focus on universal SQL optimization techniques that work across MySQL, PostgreSQL, SQL Server, Oracle, and other SQL databases.\n\n## 🎯 Core Optimization Areas\n\n### Query Performance Analysis\n```sql\n-- ❌ BAD: Inefficient query patterns\nSELECT * FROM orders o\nWHERE YEAR(o.created_at) = 2024\n  AND o.customer_id IN (\n      SELECT c.id FROM customers c WHERE c.status = 'active'\n  );\n\n-- ✅ GOOD: Optimized query with proper indexing hints\nSELECT o.id, o.customer_id, o.total_amount, o.created_at\nFROM orders o\nINNER JOIN customers c ON o.customer_id = c.id\nWHERE o.created_at >= '2024-01-01' \n  AND o.created_at < '2025-01-01'\n  AND c.status = 'active';\n\n-- Required indexes:\n-- CREATE INDEX idx_orders_created_at ON orders(created_at);\n-- CREATE INDEX idx_customers_status ON customers(status);\n-- CREATE INDEX idx_orders_customer_id ON orders(customer_id);\n```\n\n### Index Strategy Optimization\n```sql\n-- ❌ BAD: Poor indexing strategy\nCREATE INDEX idx_user_data ON users(email, first_name, last_name, created_at);\n\n-- ✅ GOOD: Optimized composite indexing\n-- For queries filtering by email first, then sorting by created_at\nCREATE INDEX idx_users_email_created ON users(email, created_at);\n\n-- For full-text name searches\nCREATE INDEX idx_users_name ON users(last_name, first_name);\n\n-- For user status queries\nCREATE INDEX idx_users_status_created ON users(status, created_at)\nWHERE status IS NOT NULL;\n```\n\n### Subquery Optimization\n```sql\n-- ❌ BAD: Correlated subquery\nSELECT p.product_name, p.price\nFROM products p\nWHERE p.price > (\n    SELECT AVG(price) \n    FROM products p2 \n    WHERE p2.category_id = p.category_id\n);\n\n-- ✅ GOOD: Window function approach\nSELECT product_name, price\nFROM (\n    SELECT product_name, price,\n           AVG(price) OVER (PARTITION BY category_id) as avg_category_price\n    FROM products\n) ranked\nWHERE price > avg_category_price;\n```\n\n## 📊 Performance Tuning Techniques\n\n### JOIN Optimization\n```sql\n-- ❌ BAD: Inefficient JOIN order and conditions\nSELECT o.*, c.name, p.product_name\nFROM orders o\nLEFT JOIN customers c ON o.customer_id = c.id\nLEFT JOIN order_items oi ON o.id = oi.order_id\nLEFT JOIN products p ON oi.product_id = p.id\nWHERE o.created_at > '2024-01-01'\n  AND c.status = 'active';\n\n-- ✅ GOOD: Optimized JOIN with filtering\nSELECT o.id, o.total_amount, c.name, p.product_name\nFROM orders o\nINNER JOIN customers c ON o.customer_id = c.id AND c.status = 'active'\nINNER JOIN order_items oi ON o.id = oi.order_id\nINNER JOIN products p ON oi.product_id = p.id\nWHERE o.created_at > '2024-01-01';\n```\n\n### Pagination Optimization\n```sql\n-- ❌ BAD: OFFSET-based pagination (slow for large offsets)\nSELECT * FROM products \nORDER BY created_at DESC \nLIMIT 20 OFFSET 10000;\n\n-- ✅ GOOD: Cursor-based pagination\nSELECT * FROM products \nWHERE created_at < '2024-06-15 10:30:00'\nORDER BY created_at DESC \nLIMIT 20;\n\n-- Or using ID-based cursor\nSELECT * FROM products \nWHERE id > 1000\nORDER BY id \nLIMIT 20;\n```\n\n### Aggregation Optimization\n```sql\n-- ❌ BAD: Multiple separate aggregation queries\nSELECT COUNT(*) FROM orders WHERE status = 'pending';\nSELECT COUNT(*) FROM orders WHERE status = 'shipped';\nSELECT COUNT(*) FROM orders WHERE status = 'delivered';\n\n-- ✅ GOOD: Single query with conditional aggregation\nSELECT \n    COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_count,\n    COUNT(CASE WHEN status = 'shipped' THEN 1 END) as shipped_count,\n    COUNT(CASE WHEN status = 'delivered' THEN 1 END) as delivered_count\nFROM orders;\n```\n\n## 🔍 Query Anti-Patterns\n\n### SELECT Performance Issues\n```sql\n-- ❌ BAD: SELECT * anti-pattern\nSELECT * FROM large_table lt\nJOIN another_table at ON lt.id = at.ref_id;\n\n-- ✅ GOOD: Explicit column selection\nSELECT lt.id, lt.name, at.value\nFROM large_table lt\nJOIN another_table at ON lt.id = at.ref_id;\n```\n\n### WHERE Clause Optimization\n```sql\n-- ❌ BAD: Function calls in WHERE clause\nSELECT * FROM orders \nWHERE UPPER(customer_email) = 'JOHN@EXAMPLE.COM';\n\n-- ✅ GOOD: Index-friendly WHERE clause\nSELECT * FROM orders \nWHERE customer_email = 'john@example.com';\n-- Consider: CREATE INDEX idx_orders_email ON orders(LOWER(customer_email));\n```\n\n### OR vs UNION Optimization\n```sql\n-- ❌ BAD: Complex OR conditions\nSELECT * FROM products \nWHERE (category = 'electronics' AND price < 1000)\n   OR (category = 'books' AND price < 50);\n\n-- ✅ GOOD: UNION approach for better optimization\nSELECT * FROM products WHERE category = 'electronics' AND price < 1000\nUNION ALL\nSELECT * FROM products WHERE category = 'books' AND price < 50;\n```\n\n## 📈 Database-Agnostic Optimization\n\n### Batch Operations\n```sql\n-- ❌ BAD: Row-by-row operations\nINSERT INTO products (name, price) VALUES ('Product 1', 10.00);\nINSERT INTO products (name, price) VALUES ('Product 2', 15.00);\nINSERT INTO products (name, price) VALUES ('Product 3', 20.00);\n\n-- ✅ GOOD: Batch insert\nINSERT INTO products (name, price) VALUES \n('Product 1', 10.00),\n('Product 2', 15.00),\n('Product 3', 20.00);\n```\n\n### Temporary Table Usage\n```sql\n-- ✅ GOOD: Using temporary tables for complex operations\nCREATE TEMPORARY TABLE temp_calculations AS\nSELECT customer_id, \n       SUM(total_amount) as total_spent,\n       COUNT(*) as order_count\nFROM orders \nWHERE created_at >= '2024-01-01'\nGROUP BY customer_id;\n\n-- Use the temp table for further calculations\nSELECT c.name, tc.total_spent, tc.order_count\nFROM temp_calculations tc\nJOIN customers c ON tc.customer_id = c.id\nWHERE tc.total_spent > 1000;\n```\n\n## 🛠️ Index Management\n\n### Index Design Principles\n```sql\n-- ✅ GOOD: Covering index design\nCREATE INDEX idx_orders_covering \nON orders(customer_id, created_at) \nINCLUDE (total_amount, status);  -- SQL Server syntax\n-- Or: CREATE INDEX idx_orders_covering ON orders(customer_id, created_at, total_amount, status); -- Other databases\n```\n\n### Partial Index Strategy\n```sql\n-- ✅ GOOD: Partial indexes for specific conditions\nCREATE INDEX idx_orders_active \nON orders(created_at) \nWHERE status IN ('pending', 'processing');\n```\n\n## 📊 Performance Monitoring Queries\n\n### Query Performance Analysis\n```sql\n-- Generic approach to identify slow queries\n-- (Specific syntax varies by database)\n\n-- For MySQL:\nSELECT query_time, lock_time, rows_sent, rows_examined, sql_text\nFROM mysql.slow_log\nORDER BY query_time DESC;\n\n-- For PostgreSQL:\nSELECT query, calls, total_time, mean_time\nFROM pg_stat_statements\nORDER BY total_time DESC;\n\n-- For SQL Server:\nSELECT \n    qs.total_elapsed_time/qs.execution_count as avg_elapsed_time,\n    qs.execution_count,\n    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,\n        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)\n        ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) as query_text\nFROM sys.dm_exec_query_stats qs\nCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt\nORDER BY avg_elapsed_time DESC;\n```\n\n## 🎯 Universal Optimization Checklist\n\n### Query Structure\n- [ ] Avoiding SELECT * in production queries\n- [ ] Using appropriate JOIN types (INNER vs LEFT/RIGHT)\n- [ ] Filtering early in WHERE clauses\n- [ ] Using EXISTS instead of IN for subqueries when appropriate\n- [ ] Avoiding functions in WHERE clauses that prevent index usage\n\n### Index Strategy\n- [ ] Creating indexes on frequently queried columns\n- [ ] Using composite indexes in the right column order\n- [ ] Avoiding over-indexing (impacts INSERT/UPDATE performance)\n- [ ] Using covering indexes where beneficial\n- [ ] Creating partial indexes for specific query patterns\n\n### Data Types and Schema\n- [ ] Using appropriate data types for storage efficiency\n- [ ] Normalizing appropriately (3NF for OLTP, denormalized for OLAP)\n- [ ] Using constraints to help query optimizer\n- [ ] Partitioning large tables when appropriate\n\n### Query Patterns\n- [ ] Using LIMIT/TOP for result set control\n- [ ] Implementing efficient pagination strategies\n- [ ] Using batch operations for bulk data changes\n- [ ] Avoiding N+1 query problems\n- [ ] Using prepared statements for repeated queries\n\n### Performance Testing\n- [ ] Testing queries with realistic data volumes\n- [ ] Analyzing query execution plans\n- [ ] Monitoring query performance over time\n- [ ] Setting up alerts for slow queries\n- [ ] Regular index usage analysis\n\n## 📝 Optimization Methodology\n\n1. **Identify**: Use database-specific tools to find slow queries\n2. **Analyze**: Examine execution plans and identify bottlenecks\n3. **Optimize**: Apply appropriate optimization techniques\n4. **Test**: Verify performance improvements\n5. **Monitor**: Continuously track performance metrics\n6. **Iterate**: Regular performance review and optimization\n\nFocus on measurable performance improvements and always test optimizations with realistic data volumes and query patterns.","tags":["sql","optimization","awesome","copilot","github"],"capabilities":["skill","source-github","category-awesome-copilot"],"categories":["awesome-copilot"],"synonyms":[],"warnings":[],"endpointUrl":"https://skills.sh/github/awesome-copilot/sql-optimization","protocol":"skill","transport":"skills-sh","auth":{"type":"none","details":{"install_from":"skills.sh"}},"qualityScore":"0.300","qualityRationale":"deterministic score 0.30 from registry signals: · indexed on skills.sh · published under github/awesome-copilot","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:v1","enrichmentVersion":1,"enrichedAt":"2026-04-22T19:40:26.317Z","embedding":null,"createdAt":"2026-04-18T20:25:08.825Z","updatedAt":"2026-04-22T19:40:26.317Z","lastSeenAt":"2026-04-22T19:40:26.317Z","tsv":"'+1':937,956,1107 '-01':104,105,110,111,337,338,389,390,759,760 '-06':427 '-1':943 '-15':428 '/2':955 '00':431 '1':498,509,520,685,715,1145 '10':429 '10.00':686,716 '1000':450,632,653,792 '10000':414 '15.00':695,719 '2':694,718,1156 '20':412,438,455 '20.00':704,722 '2024':61,103,336,388,426,758 '2025':109 '3':703,721,1164 '30':430 '3nf':1069 '4':1170 '5':1175 '50':638,664 '6':1181 'across':32 'activ':73,114,341,367,852 'aggreg':456,462,490 'agnost':667 'alert':1135 'alway':1194 'amount':86,350,745,816,834 'analysi':47,867,1142 'analyz':1124,1157 'anoth':546,566 'anti':529,538 'anti-pattern':528,537 'appli':967,1166 'approach':259,641,870 'appropri':992,1011,1061,1068,1085,1167 'area':44 'assist':11 'at.ref':551,571 'at.value':560 'avg':246,269,277,285,927,977 'avoid':986,1012,1037,1105 'awesom':3 'bad':49,149,233,294,394,459,535,577,620,672 'base':397,418,443 'batch':669,706,1099 'benefici':1048 'better':643 'book':635,661 'bottleneck':1163 'bulk':1102 'c':70,95,311,360,784 'c.id':67,99,315,364,788 'c.name':302,351,773 'c.status':72,113,340,366 'calcul':738,771,780 'call':579,905 'case':493,504,515,938 'categori':274,278,286,628,634,649,660 'category-awesome-copilot' 'chang':1104 'checklist':983 'claus':574,582,596,1002,1016 'column':555,1028,1035 'complex':621,732 'composit':169,1030 'condit':299,489,623,847 'consid':604 'constraint':1076 'continu':1177 'control':1093 'copilot':4 'core':42 'correl':234 'count':465,472,479,492,502,503,513,514,524,749,752,777,931 'cover':800,807,826,1045 'creat':117,121,125,127,135,153,165,180,182,187,191,199,214,219,223,408,424,434,605,734,756,803,812,822,831,848,855,1023,1049 'cross':966 'cursor':417,444 'cursor-bas':416 'custom':69,94,130,133,139,143,310,359,588,601,613,741,763,783,810,829 'data':157,1056,1062,1103,1122,1199 'databas':41,666,837,879,1149 'database-agnost':665 'database-specif':1148 'datalength':945 'deliv':484,518,523 'denorm':1072 'desc':410,436,900,918,980 'design':796,802 'earli':999 'effici':1066,1095 'elaps':924,928,978 'electron':629,650 'els':947 'email':160,175,186,190,589,602,609,614 'end':499,510,521,940,949,951 'entir':19 'examin':890,1158 'exec':962,969 'execut':1126,1159 'exist':1004 'expert':12 'explicit':554 'filter':173,346,998 'find':1153 'first':161,176,208 'focus':24,1188 'frequent':1026 'friend':594 'full':195 'full-text':194 'function':258,578,1013 'generic':869 'github':7 'good':74,167,256,342,415,485,553,591,639,705,727,799,842 'group':761 'handl':973 'help':1078 'hint':80 'id':64,84,98,140,144,253,255,275,314,324,331,363,376,383,442,449,453,552,572,742,764,787,811,830 'id-bas':441 'identifi':872,1146,1162 'idx':119,129,137,155,184,201,216,607,805,824,850 'impact':1041 'implement':1094 'improv':1174,1192 'includ':814 'index':79,116,118,128,136,145,151,154,170,183,200,215,593,606,793,795,801,804,823,839,844,849,1019,1021,1024,1031,1040,1046,1051,1140 'index-friend':592 'ineffici':50,295 'inner':92,357,368,377,995 'insert':678,687,696,707,708 'insert/update':1042 'instead':1005 'issu':533 'item':319,371 'iter':1182 'john@example.com':590,603 'join':93,291,296,309,317,326,344,358,369,378,545,565,782,993 'larg':401,542,562,1082 'last':163,206 'left':308,316,325 'left/right':997 'limit':411,437,454 'limit/top':1089 'lock':885 'log':895 'lower':612 'lt':544,564 'lt.id':550,558,570 'lt.name':559 'manag':794 'mean':908 'measur':1190 'methodolog':1144 'metric':1180 'monitor':863,1128,1176 'multipl':460 'mysql':33,881 'mysql.slow':894 'n':1106 'name':162,164,197,203,207,209,238,262,267,304,353,681,690,699,711 'normal':1067 'null':229 'o':56,91,301,307,356 'o.created':59,87,101,107,334,386 'o.customer':63,83,97,313,362 'o.id':82,322,348,374 'o.total':85,349 'offset':396,402,413,941,950,954 'offset-bas':395 'offset/2':936 'oi':320,372 'oi.order':323,375 'oi.product':330,382 'olap':1074 'oltp':1071 'oper':670,677,733,1100 'optim':2,10,15,28,43,75,147,168,231,292,343,392,457,575,618,644,668,982,1080,1143,1165,1168,1187,1196 'oracl':37 'order':55,90,120,124,138,142,297,306,318,355,370,406,432,451,467,474,481,526,585,599,608,611,751,754,806,809,825,828,851,854,896,914,975,1036 'over-index':1038 'p':242,328,380 'p.category':254 'p.id':332,384 'p.price':239,244 'p.product':237,303,352 'p2':250 'p2.category':252 'pagin':391,398,419,1096 'partial':838,843,1050 'partit':272,1081 'pattern':52,530,539,1055,1087,1203 'pend':470,496,501,860 'perform':9,14,46,288,532,862,866,1043,1116,1130,1173,1179,1184,1191 'pg':911 'plan':1127,1160 'poor':150 'postgresql':34,902 'prepar':1111 'prevent':1018 'price':247,263,268,270,279,284,287,631,637,652,663,682,691,700,712 'principl':797 'problem':1109 'process':861 'product':241,249,261,266,281,327,379,405,422,447,626,647,658,680,684,689,693,698,702,710,714,717,720,989 'project':20 'proper':78 'qs':965 'qs.execution':930 'qs.sql':972 'qs.statement':934,939,948,952 'qs.total':923 'qt':974 'qt.text':933,946 'queri':45,51,76,172,213,463,487,527,864,865,874,883,898,904,958,963,984,990,1027,1054,1079,1086,1108,1115,1119,1125,1129,1138,1155,1202 'rank':282 'realist':1121,1198 'regular':1139,1183 'repeat':1114 'requir':115 'result':1091 'review':1185 'right':1034 'row':674,676,887,889 'row-by-row':673 'schema':1059 'search':198 'select':17,23,53,66,81,236,245,260,265,300,347,403,420,445,464,471,478,491,531,536,540,556,557,583,597,624,645,656,740,772,882,903,922,987 'sent':888 'separ':461 'server':36,819,921 'set':1092,1133 'ship':477,507,512 'singl':486 'skill':5 'slow':399,873,1137,1154 'sort':178 'source-github' 'specif':846,875,1053,1150 'spent':748,775,791 'sql':1,8,13,27,35,40,48,148,232,293,393,458,534,576,619,671,726,798,818,841,868,891,920,970 'start':935,953 'stat':912,964 'statement':913,1112 'status':131,134,212,218,222,226,469,476,483,495,506,517,817,835,858 'storag':1065 'strategi':146,152,840,1022,1097 'structur':985 'subqueri':230,235,1009 'substr':932 'sum':743 'syntax':820,876 'sys.dm':961,968 'tabl':543,547,563,567,724,730,736,768,1083 'tc':781 'tc.customer':786 'tc.order':776 'tc.total':774,790 'techniqu':29,290,1169 'temp':737,767,779 'temporari':723,729,735 'test':1117,1118,1171,1195 'text':196,892,959,971 'time':884,886,899,907,909,917,929,979,1132 'time/qs.execution_count':925 'tool':1151 'total':744,747,815,833,906,916 'track':1178 'tune':289 'type':994,1057,1063 'union':617,640,654 'univers':26,981 'upper':587 'usag':725,1020,1141 'use':440,728,765,991,1003,1029,1044,1060,1075,1088,1098,1110,1147 'user':156,159,185,189,202,205,211,217,221 'valu':683,692,701,713 'vari':877 'verifi':1172 'volum':1123,1200 'vs':616,996 'window':257 'work':31 'year':58","prices":[{"id":"f6996a05-95da-4f51-a1d5-4eeb645b2719","listingId":"414b2cf6-ae15-4482-8f3f-919b3c8abc13","amountUsd":"0","unit":"free","nativeCurrency":null,"nativeAmount":null,"chain":null,"payTo":null,"paymentMethod":"skill-free","isPrimary":true,"details":{"org":"github","category":"awesome-copilot","install_from":"skills.sh"},"createdAt":"2026-04-18T20:25:08.825Z"}],"sources":[{"listingId":"414b2cf6-ae15-4482-8f3f-919b3c8abc13","source":"github","sourceId":"github/awesome-copilot/sql-optimization","sourceUrl":"https://github.com/github/awesome-copilot/tree/main/skills/sql-optimization","isPrimary":false,"firstSeenAt":"2026-04-18T21:51:19.753Z","lastSeenAt":"2026-04-22T18:53:07.682Z"},{"listingId":"414b2cf6-ae15-4482-8f3f-919b3c8abc13","source":"skills_sh","sourceId":"github/awesome-copilot/sql-optimization","sourceUrl":"https://skills.sh/github/awesome-copilot/sql-optimization","isPrimary":true,"firstSeenAt":"2026-04-18T20:25:08.825Z","lastSeenAt":"2026-04-22T19:40:26.317Z"}],"details":{"listingId":"414b2cf6-ae15-4482-8f3f-919b3c8abc13","quickStartSnippet":null,"exampleRequest":null,"exampleResponse":null,"schema":null,"openapiUrl":null,"agentsTxtUrl":null,"citations":[],"useCases":[],"bestFor":[],"notFor":[],"kindDetails":{"org":"github","slug":"sql-optimization","source":"skills_sh","category":"awesome-copilot","skills_sh_url":"https://skills.sh/github/awesome-copilot/sql-optimization"},"updatedAt":"2026-04-22T19:40:26.317Z"}}