{"kind":"Skill","metadata":{"namespace":"community","name":"sql-optimization","version":"0.1.0"},"spec":{"description":"Universal SQL performance optimization assistant for comprehensive query tuning, indexing strategies, and database performance analysis across all SQL databases (MySQL, PostgreSQL, SQL Server, Oracle). Provides execution plan analysis, pagination optimization, batch operations, and performance monitoring guidance.","files":{"SKILL.md":"---\nname: sql-optimization\ndescription: 'Universal SQL performance optimization assistant for comprehensive query tuning, indexing strategies, and database performance analysis across all SQL databases (MySQL, PostgreSQL, SQL Server, Oracle). Provides execution plan analysis, pagination optimization, batch operations, and performance monitoring guidance.'\n---\n\n# 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 \u003e= '2024-01-01' \n  AND o.created_at \u003c '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 \u003e (\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 \u003e 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 \u003e '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 \u003e '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 \u003c '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 \u003e 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 \u003c 1000)\n   OR (category = 'books' AND price \u003c 50);\n\n-- ✅ GOOD: UNION approach for better optimization\nSELECT * FROM products WHERE category = 'electronics' AND price \u003c 1000\nUNION ALL\nSELECT * FROM products WHERE category = 'books' AND price \u003c 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 \u003e= '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 \u003e 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.\n"},"import":{"commit_sha":"541b7819d8c3545c6df122491af4fa1eae415779","imported_at":"2026-05-18T20:05:35Z","license_text":"MIT License\n\nCopyright GitHub, Inc.\n\nPermission is hereby granted, free of charge, to any person obtaining a copy\nof this software and associated documentation files (the \"Software\"), to deal\nin the Software without restriction, including without limitation the rights\nto use, copy, modify, merge, publish, distribute, sublicense, and/or sell\ncopies of the Software, and to permit persons to whom the Software is\nfurnished to do so, subject to the following conditions:\n\nThe above copyright notice and this permission notice shall be included in all\ncopies or substantial portions of the Software.\n\nTHE SOFTWARE IS PROVIDED \"AS IS\", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR\nIMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,\nFITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE\nAUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER\nLIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,\nOUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE\nSOFTWARE.","owner":"github","repo":"github/awesome-copilot","source_url":"https://github.com/github/awesome-copilot/tree/541b7819d8c3545c6df122491af4fa1eae415779/plugins/database-data-management/skills/sql-optimization"}},"content_hash":[27,204,117,84,48,149,72,183,98,42,190,167,142,208,17,41,110,29,43,119,237,81,80,66,186,4,234,183,141,228,188,194],"trust_level":"unsigned","yanked":false}