sql-optimization

name	sql-optimization
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.
metadata	{"author":"Mauro Brambilla","author-url":"https://brumaombra.com"}

SQL Performance Optimization Assistant

Expert 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.

⚙️ Query Builder: Knex.js

This app uses Knex.js as its query builder. When suggesting optimized queries, always prefer Knex syntax over raw SQL strings unless the operation is not expressible with Knex's fluent API.

Syntax preference rules

• ✅ Use Knex fluent API for all standard queries: selects, joins, wheres, aggregates, inserts, updates, deletes.
• ✅ Use knex.raw() only for database-specific expressions that Knex cannot express natively (e.g. window functions, complex CASE expressions, vendor-specific index hints).
• ❌ Never write a full raw SQL string when the equivalent Knex chain is available.

Examples

// ❌ BAD: raw SQL when Knex can handle it
knex.raw('SELECT id, name FROM users WHERE status = ? AND created_at > ?', ['active', date]);

// ✅ GOOD: Knex fluent API
knex('users')
    .select('id', 'name')
    .where('status', 'active')
    .where('created_at', '>', date);

// ✅ GOOD: knex.raw() only for what Knex can't express natively
knex('products')
    .select('*')
    .select(knex.raw('AVG(price) OVER (PARTITION BY category_id) as avg_category_price'));

🎯 Core Optimization Areas

Query Performance Analysis

-- ❌ BAD: Inefficient query patterns
SELECT * FROM orders o
WHERE YEAR(o.created_at) = 2024
  AND o.customer_id IN (
      SELECT c.id FROM customers c WHERE c.status = 'active'
  );

-- ✅ GOOD: Optimized query with proper indexing hints
SELECT o.id, o.customer_id, o.total_amount, o.created_at
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= '2024-01-01' 
  AND o.created_at < '2025-01-01'
  AND c.status = 'active';

-- Required indexes:
-- CREATE INDEX idx_orders_created_at ON orders(created_at);
-- CREATE INDEX idx_customers_status ON customers(status);
-- CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Index Strategy Optimization

-- ❌ BAD: Poor indexing strategy
CREATE INDEX idx_user_data ON users(email, first_name, last_name, created_at);

-- ✅ GOOD: Optimized composite indexing
-- For queries filtering by email first, then sorting by created_at
CREATE INDEX idx_users_email_created ON users(email, created_at);

-- For full-text name searches
CREATE INDEX idx_users_name ON users(last_name, first_name);

-- For user status queries
CREATE INDEX idx_users_status_created ON users(status, created_at)
WHERE status IS NOT NULL;

Subquery Optimization

-- ❌ BAD: Correlated subquery
SELECT p.product_name, p.price
FROM products p
WHERE p.price > (
    SELECT AVG(price) 
    FROM products p2 
    WHERE p2.category_id = p.category_id
);

-- ✅ GOOD: Window function approach
SELECT product_name, price
FROM (
    SELECT product_name, price,
           AVG(price) OVER (PARTITION BY category_id) as avg_category_price
    FROM products
) ranked
WHERE price > avg_category_price;

📊 Performance Tuning Techniques

JOIN Optimization

-- ❌ BAD: Inefficient JOIN order and conditions
SELECT o.*, c.name, p.product_name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01'
  AND c.status = 'active';

-- ✅ GOOD: Optimized JOIN with filtering
SELECT o.id, o.total_amount, c.name, p.product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id AND c.status = 'active'
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01';

Pagination Optimization

-- ❌ BAD: OFFSET-based pagination (slow for large offsets)
SELECT * FROM products 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 10000;

-- ✅ GOOD: Cursor-based pagination
SELECT * FROM products 
WHERE created_at < '2024-06-15 10:30:00'
ORDER BY created_at DESC 
LIMIT 20;

-- Or using ID-based cursor
SELECT * FROM products 
WHERE id > 1000
ORDER BY id 
LIMIT 20;

Aggregation Optimization

-- ❌ BAD: Multiple separate aggregation queries
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders WHERE status = 'shipped';
SELECT COUNT(*) FROM orders WHERE status = 'delivered';

-- ✅ GOOD: Single query with conditional aggregation
SELECT 
    COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_count,
    COUNT(CASE WHEN status = 'shipped' THEN 1 END) as shipped_count,
    COUNT(CASE WHEN status = 'delivered' THEN 1 END) as delivered_count
FROM orders;

🔍 Query Anti-Patterns

SELECT Performance Issues

-- ❌ BAD: SELECT * anti-pattern
SELECT * FROM large_table lt
JOIN another_table at ON lt.id = at.ref_id;

-- ✅ GOOD: Explicit column selection
SELECT lt.id, lt.name, at.value
FROM large_table lt
JOIN another_table at ON lt.id = at.ref_id;

WHERE Clause Optimization

-- ❌ BAD: Function calls in WHERE clause
SELECT * FROM orders 
WHERE UPPER(customer_email) = '[e-mail]';

-- ✅ GOOD: Index-friendly WHERE clause
SELECT * FROM orders 
WHERE customer_email = '[e-mail]';
-- Consider: CREATE INDEX idx_orders_email ON orders(LOWER(customer_email));

OR vs UNION Optimization

-- ❌ BAD: Complex OR conditions
SELECT * FROM products 
WHERE (category = 'electronics' AND price < 1000)
   OR (category = 'books' AND price < 50);

-- ✅ GOOD: UNION approach for better optimization
SELECT * FROM products WHERE category = 'electronics' AND price < 1000
UNION ALL
SELECT * FROM products WHERE category = 'books' AND price < 50;

📈 Database-Agnostic Optimization

Batch Operations

-- ❌ BAD: Row-by-row operations
INSERT INTO products (name, price) VALUES ('Product 1', 10.00);
INSERT INTO products (name, price) VALUES ('Product 2', 15.00);
INSERT INTO products (name, price) VALUES ('Product 3', 20.00);

-- ✅ GOOD: Batch insert
INSERT INTO products (name, price) VALUES 
('Product 1', 10.00),
('Product 2', 15.00),
('Product 3', 20.00);

Temporary Table Usage

-- ✅ GOOD: Using temporary tables for complex operations
CREATE TEMPORARY TABLE temp_calculations AS
SELECT customer_id, 
       SUM(total_amount) as total_spent,
       COUNT(*) as order_count
FROM orders 
WHERE created_at >= '2024-01-01'
GROUP BY customer_id;

-- Use the temp table for further calculations
SELECT c.name, tc.total_spent, tc.order_count
FROM temp_calculations tc
JOIN customers c ON tc.customer_id = c.id
WHERE tc.total_spent > 1000;

🛠️ Index Management

Index Design Principles

-- ✅ GOOD: Covering index design
CREATE INDEX idx_orders_covering 
ON orders(customer_id, created_at) 
INCLUDE (total_amount, status);  -- SQL Server syntax
-- Or: CREATE INDEX idx_orders_covering ON orders(customer_id, created_at, total_amount, status); -- Other databases

Partial Index Strategy

-- ✅ GOOD: Partial indexes for specific conditions
CREATE INDEX idx_orders_active 
ON orders(created_at) 
WHERE status IN ('pending', 'processing');

📊 Performance Monitoring Queries

Query Performance Analysis

-- Generic approach to identify slow queries
-- (Specific syntax varies by database)

-- For MySQL:
SELECT query_time, lock_time, rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY query_time DESC;

-- For PostgreSQL:
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC;

-- For SQL Server:
SELECT 
    qs.total_elapsed_time/qs.execution_count as avg_elapsed_time,
    qs.execution_count,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
        ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) as query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_elapsed_time DESC;

🎯 Universal Optimization Checklist

Query Structure

• Avoiding SELECT * in production queries
• Using appropriate JOIN types (INNER vs LEFT/RIGHT)
• Filtering early in WHERE clauses
• Using EXISTS instead of IN for subqueries when appropriate
• Avoiding functions in WHERE clauses that prevent index usage

Index Strategy

• Creating indexes on frequently queried columns
• Using composite indexes in the right column order
• Avoiding over-indexing (impacts INSERT/UPDATE performance)
• Using covering indexes where beneficial
• Creating partial indexes for specific query patterns

Data Types and Schema

• Using appropriate data types for storage efficiency
• Normalizing appropriately (3NF for OLTP, denormalized for OLAP)
• Using constraints to help query optimizer
• Partitioning large tables when appropriate

Query Patterns

• Using LIMIT/TOP for result set control
• Implementing efficient pagination strategies
• Using batch operations for bulk data changes
• Avoiding N+1 query problems
• Using prepared statements for repeated queries

Performance Testing

• Testing queries with realistic data volumes
• Analyzing query execution plans
• Monitoring query performance over time
• Setting up alerts for slow queries
• Regular index usage analysis

📝 Optimization Methodology

1. Identify: Use database-specific tools to find slow queries
2. Analyze: Examine execution plans and identify bottlenecks
3. Optimize: Apply appropriate optimization techniques — write solutions in Knex fluent API syntax; fall back to knex.raw() only when necessary
4. Test: Verify performance improvements
5. Monitor: Continuously track performance metrics
6. Iterate: Regular performance review and optimization

Focus on measurable performance improvements and always test optimizations with realistic data volumes and query patterns.