Indexes & Query Optimization

Index কী?

Database-এর "table of contents" — column-এর উপর তৈরি sorted lookup structure। SELECT দ্রুত করে কিন্তু INSERT/UPDATE ধীর করে।

Index Types

-- Clustered (table-এ একটি, data physically sorted)
CREATE CLUSTERED INDEX IX_Products_Id ON Products(Id);
-- PK by default এটি তৈরি করে

-- Non-clustered (একাধিক, separate B-tree)
CREATE INDEX IX_Products_Name ON Products(Name);

-- Composite (একাধিক column)
CREATE INDEX IX_Products_Cat_Price ON Products(CategoryId, Price);
-- Order matters! WHERE CategoryId = 5 — ব্যবহার হবে
-- WHERE Price = 100 — ব্যবহার হবে না

-- Unique
CREATE UNIQUE INDEX UX_Users_Email ON Users(Email);

-- Covering (INCLUDE additional columns)
CREATE INDEX IX_Products_Cat
    ON Products(CategoryId)
    INCLUDE (Name, Price);
-- "covering" — query শুধু index থেকে answer করতে পারে

-- Filtered (subset)
CREATE INDEX IX_Active_Users ON Users(LastLogin)
    WHERE Status = 'active';

-- Drop
DROP INDEX IX_Products_Name ON Products;

কোথায় Index দেবেন

• WHERE clause-এ যে column-এ filter
• JOIN-এর ON column
• ORDER BY / GROUP BY
• FK column

কোথায় দেবেন না

• খুব ছোট table
• খুব বেশি INSERT/UPDATE
• Low cardinality (gender, status — মাত্র কয়েকটি value)

Execution Plan দেখা

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Estimated plan: Ctrl+L (SSMS)
-- Actual plan: Ctrl+M then run

SELECT * FROM Products WHERE Name = 'Pen';

-- দেখুন:
-- Index Seek (good ⭐)
-- Index Scan (medium)
-- Table/Clustered Index Scan (slow ❌)

Slow Query চিনুন

-- DMV — সবচেয়ে দামি queries
SELECT TOP 20
    qs.execution_count,
    qs.total_logical_reads,
    qs.total_elapsed_time / 1000 AS total_ms,
    SUBSTRING(qt.text, qs.statement_start_offset/2,
        (CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) AS query
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY total_logical_reads DESC;

-- Missing index hint
SELECT * FROM sys.dm_db_missing_index_details;

Common Mistakes ⚠️

-- ❌ SELECT *
SELECT * FROM Products;

-- ✅ Specific columns
SELECT Id, Name, Price FROM Products;

-- ❌ Function on indexed column (index ব্যবহার হবে না)
WHERE YEAR(CreatedAt) = 2024
WHERE UPPER(Name) = 'PEN'

-- ✅
WHERE CreatedAt >= '2024-01-01' AND CreatedAt < '2025-01-01'

-- ❌ Leading wildcard
WHERE Name LIKE '%phone'

-- ✅
WHERE Name LIKE 'phone%'

-- ❌ N+1 in EF (loop-এ query)
foreach (var c in categories)
    var products = _db.Products.Where(p => p.CategoryId == c.Id).ToList();

-- ✅ Include
var data = _db.Categories.Include(c => c.Products).ToList();

Index Maintenance

-- Fragmentation
SELECT * FROM sys.dm_db_index_physical_stats(...);

-- Rebuild (heavy, locks)
ALTER INDEX IX_Products_Name ON Products REBUILD;

-- Reorganize (online, lighter)
ALTER INDEX IX_Products_Name ON Products REORGANIZE;

-- Update statistics
UPDATE STATISTICS Products;