SQL Index Performance Analysis and Query Optimization

🔹 1️⃣ What Are Indexes in SQL?

An index is a database object that keeps column values sorted so the optimizer can locate rows quickly instead of scanning the whole table. Faster reads come with a cost: every INSERT, UPDATE, or DELETE must also update the index.

⚙️ 2️⃣ How Indexes Work

• Most relational engines default to B-tree indexes (balanced trees).
• When a query has a WHERE filter or JOIN condition, the optimizer can traverse the index path rather than perform a table scan.
• Index selection depends on statistics and cost estimates.

⚙️ 3️⃣ Check Query Performance — EXPLAIN or EXPLAIN ANALYZE

Always compare execution plans before and after indexing to validate improvements.

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

Plan output highlights whether the engine performs a sequential scan or an Index Scan/Index Only Scan.

⚙️ 4️⃣ Creating an Index

CREATE INDEX idx_emp_dept ON employees (department_id);

-- Use indexes on columns that:
-- • Appear in WHERE, JOIN, or ORDER BY clauses
-- • Have high selectivity (many unique values)

⚙️ 5️⃣ Composite (Multi-Column) Index

CREATE INDEX idx_emp_dept_name ON employees (department_id, last_name);
-- Order matters: (department_id, last_name) ≠ (last_name, department_id)

⚙️ 6️⃣ Example — With and Without Index

-- Without index (slow)
SELECT * FROM orders WHERE customer_id = 101;

-- Create index
CREATE INDEX idx_orders_custid ON orders (customer_id);

-- Now query becomes faster
SELECT * FROM orders WHERE customer_id = 101;
-- Validate improvement with EXPLAIN

⚙️ 7️⃣ Types of Indexes

⚙️ 8️⃣ Covering Index (Index-Only Query)

CREATE INDEX idx_emp_cover ON employees (department_id, salary);

SELECT department_id, salary
FROM employees
WHERE department_id = 10;
-- Query runs entirely from the index

When all referenced columns are in the index, the engine can skip table lookups, improving performance.

⚙️ 9️⃣ Dropping and Monitoring Indexes

DROP INDEX idx_emp_dept;
-- Monitor usage:
-- • sys.dm_db_index_usage_stats (SQL Server)
-- • pg_stat_user_indexes (PostgreSQL)
-- • EXPLAIN PLAN (Oracle/MySQL)

Regularly review usage stats to remove unused indexes that slow down write-heavy workloads.

⚙️ 🔟 Best Practices for Index Optimization

🧠 11️⃣ Exampl– Query Optimization Flow

-- Step 1 – Original query
SELECT * FROM sales WHERE region = 'West' AND amount > 5000;

-- Step 2 – Create index
CREATE INDEX idx_sales_region_amt ON sales (region, amount);

-- Step 3 – Check performance
EXPLAIN ANALYZE SELECT * FROM sales WHERE region = 'West' AND amount > 5000;
-- Expect Index Scan instead of Seq Scan

💡 12️⃣ Key Takeaways

🧠 Interview Tip