Index Types
| Type | ব্যবহার |
|---|---|
| Clustered | physical sort, 1 per table |
| Non-Clustered | secondary index |
| Composite | multi-column |
| Filtered | WHERE condition |
| Full-Text | text search |
| Columnstore | OLAP analytics |
কোথায় Index?
- WHERE, JOIN, ORDER BY column
- FK column, high selectivity
- ❌ Low cardinality (gender)
- ❌ Frequently updated
Composite Order
CREATE INDEX ix ON orders(customer_id, order_date DESC); ✅ WHERE customer_id=? AND order_date>? ❌ WHERE order_date>? -- left-most missing
Execution Plan ⭐
EXPLAIN ANALYZE SELECT ... -- PG/MySQL SET STATISTICS IO, TIME ON; -- SQL Server 🔴 Table/Seq Scan = ধীর 🟢 Index Seek = দ্রুত ⚠ Key Lookup → INCLUDE column ⚠ Hash Match → index missing
Optimization Tips
- SELECT * এড়ান
- WHERE-এ function এড়ান (UPPER, YEAR)
- LIKE 'abc%' OK, '%abc%' bad
- EXISTS prefer NOT IN
- UNION ALL prefer UNION
- Cursor-based pagination
Partitioning
CREATE TABLE orders ... PARTITION BY RANGE (YEAR(order_date)) ( PARTITION p2024 VALUES LESS THAN (2025), PARTITION p2025 VALUES LESS THAN (2026) );
Sharding ⭐
Horizontal partition multi-server। Hash, Range, Geographic, Tenant। Challenge: cross-shard query।
Replication
Master-Slave, Master-Master। Read replica → scale read। Lag → eventual consistency।