Part 2 · ডেটাবেস 📖 ১৪ মিনিট পড়া 📝 ২০টি কুইজ

Database Indexes

৫০০-পৃষ্ঠার বইয়ে index ছাড়া যা — DB-তে index ছাড়াও তা।

📝 কুইজে যান

আপনার একটি ৫০০-পৃষ্ঠার বইয়ে "TCP" শব্দটি কোথায় আছে খুঁজতে হবে। বইয়ের শেষে index থাকলে — দেখলেন "TCP — pages 47, 142, 238"। সরাসরি যেতে পারলেন। Index না থাকলে — পৃষ্ঠা ১ থেকে শুরু করতে হতো। Database-এ index একই কাজ করে।

Index কী?

Database Index হলো একটি বিশেষ data structure (সাধারণত B-tree) যা table-এর কোনো column-এর মান সাজিয়ে রাখে — ফলে query খুব দ্রুত record খুঁজতে পারে।

Index ছাড়া DB full table scan করে — million row হলেও সব read করে।

কীভাবে কাজ করে?

একটা users table — million row। আপনি query করলেন:

SELECT * FROM users WHERE email = 'm@example.com';
  • Without index: Million row scan করে — slow।
  • With index on email: B-tree-তে log(N) lookup। ১M row-এ ২০ comparison।

Index-এর ধরন

১. B-Tree Index (Default)

Balanced tree — সবচেয়ে কমন। Range query (BETWEEN, >, <), ORDER BY-তে দ্রুত।

  • সুবিধা: Equality + range — দুই-ই দ্রুত।
  • ব্যবহার: ID, date, ordered column।

২. Hash Index

Hash function দিয়ে — শুধু equality (=)। Range কাজ করে না।

  • সুবিধা: O(1) equality lookup।
  • অসুবিধা: Range, sort নেই।
  • ব্যবহার: Memcached, Redis, কিছু DB-তে memory table।

৩. Composite (Multi-column) Index

একাধিক column-এর উপর — order matters।

CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);

এই index ব্যবহার হবে:

  • WHERE user_id = X — ✓
  • WHERE user_id = X AND status = Y — ✓
  • WHERE user_id = X AND status = Y AND created_at > ... — ✓
  • WHERE status = Y (without user_id) — ✗ (left-most prefix nai)
💡 Left-most prefix rule: Composite index-এ left থেকে column ব্যবহার করতেই হবে।

৪. Unique Index

Index + uniqueness constraint। Email, username field-এ।

৫. Full-text Index

Text content search — MySQL FULLTEXT, PostgreSQL GIN।

৬. Partial Index

Subset of rows — যেমন WHERE deleted_at IS NULL। Storage saving।

৭. Covering Index

Query-এর সব column index-এ আছে — table-এ যেতে হয় না (index-only scan)।

কখন Index যোগ করবেন?

  • WHERE clause-এ frequently appearing column।
  • JOIN-এ ব্যবহৃত column (ON foo.id = bar.foo_id)।
  • ORDER BY column।
  • GROUP BY column।
  • Foreign key column।

কখন Index যোগ করবেন না?

  • Write-heavy table: প্রতিটি INSERT/UPDATE-এ index update — slow।
  • Small table: Few thousand row-এ full scan ই দ্রুত।
  • Low cardinality: Boolean (true/false) — index লাভ নেই।
  • Frequently updated column: Index maintenance overhead।
  • Rarely queried column: Storage খায় — কোনো লাভ নেই।

Index-এর Cost

  • Storage: Index data structure disk-এ। Table size-এর ১০-৩০%।
  • Write performance: Insert/Update/Delete-এ index-ও update। ৩-৫ index = write slow।
  • Memory: Hot index buffer pool-এ থাকে।
  • Maintenance: Periodic rebuild/reorganize।

EXPLAIN — Index ব্যবহার দেখা

SQL query optimize করতে EXPLAIN command:

EXPLAIN SELECT * FROM users WHERE email = 'foo@bar.com'; -- Output shows: -- type: ref (good — using index) -- type: ALL (bad — full scan) -- key: idx_email (which index used) -- rows: 1 (rows examined)

Clustered vs Non-Clustered Index

Clustered Index

  • Table row-এর physical order index অনুযায়ী
  • একটিই হতে পারে (primary key সাধারণত)
  • Range query super fast
  • InnoDB MySQL-এর default

Non-Clustered Index

  • আলাদা structure → table row-এ pointer
  • একাধিক হতে পারে
  • Equality fast, range কিছুটা slow
  • Secondary index

বাস্তব উদাহরণ

  • E-commerce: Index on (user_id, status) — user-এর order quickly।
  • Social media: Index on (user_id, created_at DESC) — timeline।
  • Search: Full-text index on product description।
  • Banking: Composite index (account_id, transaction_date)।

সাধারণ ভুল ধারণা

  1. "যত index তত fast": না — write slow, storage waste।
  2. "Index automatic": Primary key auto, কিন্তু অন্য column-এ explicit।
  3. "Index মানে query fast": Wrong index/usage হলে DB skip করে।

Best Practices

  • Slow query log monitor — কোথায় index দরকার বুঝবেন।
  • EXPLAIN দিয়ে query plan দেখুন।
  • Composite index column order — selectivity high → low।
  • Foreign key column-এ index দিন।
  • Unused index drop করুন।
  • Periodic ANALYZE — DB-কে statistics update দিতে।

📌 চ্যাপ্টার সারমর্ম

  • Index = data structure দ্রুত search-এর জন্য।
  • B-Tree default; Hash equality-only; Composite multi-column।
  • Read fast করে কিন্তু write slow।
  • Left-most prefix rule composite index-এ।
  • EXPLAIN দিয়ে index usage check।