Indexes — B-Trees, Hash, Composite, Covering

Index — query-এর গতি বাড়ানোর সবচেয়ে শক্তিশালী হাতিয়ার

Read: ~40 min Hard 16 practice problems Live SQL runner

1. Why Indexes Exist — and What They Cost

Imagine you have to find a specific student named "Nusrat Jahan" in a printed list of one million students. Without an index, you scan from page 1 — that is a full table scan, O(n) work. With a name index at the back of the book, you flip to "N", binary-search to "Nu", and reach the page in seconds. That is the entire idea behind a database index.

১০ লক্ষ student-এর তালিকা থেকে "নুসরাত জাহান" নামের একজনকে খুঁজতে হলে — index ছাড়া আপনাকে পাতা ১ থেকে শুরু করতে হবে (এটিই full table scan)। কিন্তু বইয়ের শেষে যদি একটি নাম-index থাকে, আপনি সরাসরি "N" → "Nu" পর্যন্ত ছুটে গিয়ে কয়েক সেকেন্ডে পৌঁছে যাবেন। Database index ঠিক এটাই করে।

But indexes are not free. Every index takes disk space, and every INSERT / UPDATE / DELETE must update the index. A well-chosen index can speed a query 10,000×; a careless one can slow writes by 5×. This module teaches how to choose well.

The Index Trade-off (একটি কথায়)
Indexes make reads faster and writes slower.
যত বেশি index, তত দ্রুত SELECT, কিন্তু তত ধীর INSERT/UPDATE/DELETE। তাই index গুলো হিসেব করে রাখতে হয়।

2. The B-Tree — The Default Index Everywhere

Almost every relational database uses a B-tree (or B+tree) as its default index. Think of it as a balanced tree where each node holds many keys (hundreds), each pointing to children. To find a key you start at the root and follow one branch per level. With a fan-out of 100 and a million rows, the tree is just three levels deep — three disk reads instead of a million.

B-tree একটি balanced tree, যার প্রতিটি node-এ অনেকগুলো key (শত শত) থাকে। ১০ লক্ষ row হলেও tree-এর গভীরতা মাত্র ৩-৪ level — মানে শুধু ৩-৪ বার disk-এ যেতে হয়। এটাই কেন B-tree সব বড় database-এ default।

Figure 23.1 — A simplified B-tree. To find key 130: root → middle → leaf — 3 reads.

See the difference live

We will create a 1,000-row table, run a query without an index (full scan), then create the index and run the same query (B-tree lookup). Use the run button — the difference shows in the EXPLAIN QUERY PLAN output.

scan_vs_index.sql

-- Step 1: WITHOUT an index, plan = SCAN students:
EXPLAIN QUERY PLAN
SELECT * FROM students WHERE name = 'Student_777';

-- Step 2: Add a B-tree index:
CREATE INDEX idx_students_name ON students(name);

-- Step 3: WITH index, plan = SEARCH ... USING INDEX:
EXPLAIN QUERY PLAN
SELECT * FROM students WHERE name = 'Student_777';

Look at the second EXPLAIN QUERY PLAN output: it switches from "SCAN students" (read every row) to "SEARCH students USING INDEX idx_students_name" (jump straight to the row). On a million-row table, that is the difference between 1 second and 1 millisecond.

3. Hash Indexes — Postgres Has Them, SQLite Does Not

A hash index stores keys by their hash value. Lookup of an exact key is O(1) — even faster than a B-tree. But hash indexes cannot answer range queries (WHERE age > 18) and cannot help with ORDER BY. PostgreSQL has them via CREATE INDEX ... USING HASH; SQLite has only B-trees.

Hash index একদম exact-match lookup-এ দ্রুত — O(1)। কিন্তু এটি range (>, <, BETWEEN) বা ORDER BY-তে কাজে আসে না। PostgreSQL-এ USING HASH দিয়ে hash index বানানো যায়; SQLite-এ শুধুই B-tree।

Operation	B-tree	Hash
`WHERE x = ?`	Fast (O(log n))	Fastest (O(1))
`WHERE x > ?` or `BETWEEN`	Fast — sorted leaves	Useless
`ORDER BY x`	Free — already sorted	Useless
`WHERE x LIKE 'pre%'`	Fast (prefix scan)	Useless

Practical advice (পরামর্শ)
On Postgres, use the default B-tree unless you have measured that hash is faster on your specific workload and you only do equality lookups. On SQLite, you have no choice — and you rarely need one.

4. Composite Indexes — Column Order Matters

A composite index indexes more than one column at once. The crucial rule: a composite index (a, b, c) can serve queries on a, (a, b), and (a, b, c) — but NOT on b alone or c alone. Imagine a phonebook sorted by (last name, first name): you can find all Khans easily, all "Khan, Arif" easily — but finding all "Arif" anywhere in the book requires a full scan again.

Composite index (a,b,c) মানে — index প্রথমে a দিয়ে sort, তারপর সমান a-এর ভেতরে b দিয়ে, এর পর c। তাই এটি WHERE a=..., WHERE a=... AND b=..., এবং WHERE a=... AND b=... AND c=... কে সাহায্য করে — কিন্তু শুধু WHERE b=... পেলে কাজে আসে না। ফোনবুকের মতো — last name-এ sort, first name-এ পরে।

composite_demo.sql

-- Index is (customer_id, status). Test which queries use it:

-- Uses the index (leftmost prefix matched):
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 7;

-- Uses the index (both columns matched):
EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE customer_id = 7 AND status = 'paid';

-- Does NOT use the index (skips the leading column):
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid';

The third query falls back to a full table scan despite the index existing — because status is the second column and you cannot use the second column without filtering on the first. Always order composite columns from most selective and most frequently filtered first.

5. Covering Indexes — Index-Only Scans

Normally, the database uses an index to find the row's location, then reads the row from the heap to get the other columns. A covering index contains all the columns the query needs — so the database never touches the table. This is called an index-only scan and is the fastest kind of indexed read.

সাধারণত index দিয়ে row-এর location পাওয়া যায়, তারপর actual row পড়তে heap-এ যেতে হয়। কিন্তু যদি index নিজেই সব দরকারি কলাম ধারণ করে, তাহলে heap-এ যাবার দরকার নেই — এটাই covering index বা index-only scan। সবচেয়ে দ্রুত।

covering_index.sql

-- Query asks ONLY for indexed columns -> covering, index-only scan possible:
EXPLAIN QUERY PLAN
SELECT member_id, loan_date
FROM library_loans
WHERE member_id = 7;

-- Query asks for an extra column not in the index -> must visit table:
EXPLAIN QUERY PLAN
SELECT member_id, loan_date, book_id
FROM library_loans
WHERE member_id = 7;

In SQLite, the EXPLAIN line will mention "USING COVERING INDEX" when the index covers the query. In Postgres, look for "Index Only Scan" in EXPLAIN ANALYZE output.

6. Partial Indexes — Index Only the Rows You Care About

A partial index covers only rows matching a WHERE clause. Example: in an e-commerce orders table, 99% of rows are status = 'completed'. You almost always query for the tiny 1% that is status = 'pending'. A partial index on just the pending rows is small, fast, and barely costs anything to maintain.

Partial index মানে — শুধু একটি subset row-এর উপর index। যেমন: আপনার orders table-এর ৯৯% row "completed"; আপনি প্রায়শই query করেন "pending" status নিয়ে। তাহলে শুধু pending row-এর উপর index বানালে — index ছোট, দ্রুত, এবং write-cost খুব কম।

partial_index.sql

-- Uses the partial index — predicate matches:
EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE status = 'pending' AND customer_id = 7;

-- Does NOT use it — query asks for completed:
EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE status = 'completed' AND customer_id = 7;

7. Reading `EXPLAIN QUERY PLAN`

EXPLAIN QUERY PLAN is your single best tool. SQLite outputs a tiny tree describing how it plans to run the query. The keywords you watch for are:

Keyword	Meaning	Verdict
`SCAN table`	Reading every row of the table	⚠️ slow on big tables
`SEARCH table USING INDEX idx`	Used the index to jump in	✅ good
`SEARCH ... USING COVERING INDEX`	Index alone answered the query	✅✅ best
`SEARCH ... USING INTEGER PRIMARY KEY`	Used the rowid	✅ best for PK lookups
`USE TEMP B-TREE FOR ORDER BY`	Sorted on the fly — index didn't cover ordering	⚠️ consider an index that pre-sorts

EXPLAIN QUERY PLAN চালানোর পর "SCAN" দেখলেই বুঝবেন full table scan হচ্ছে — সম্ভবত একটি index দরকার। "SEARCH USING INDEX" দেখলে কাজ হচ্ছে। আর "USING COVERING INDEX" হলে — সবচেয়ে ভালো অবস্থা।

Pro tip
Run EXPLAIN QUERY PLAN on every slow query before blaming the database. 90% of "slow DB" complaints are missing or wrong indexes — visible in one line of plan output.

8. When NOT to Add an Index

More indexes is not always better. The cost is paid on every write, the memory used by the buffer cache, and the CPU spent maintaining the structure. Avoid indexes when:

✅ Add an index (যোগ করুন)

Column appears in WHERE, JOIN, or ORDER BY often.
Selectivity is high (filter cuts >90% of rows).
Table is large (>10k rows).
Read-to-write ratio is high.

⚠️ Skip the index (এড়িয়ে যান)

Column has very few distinct values (e.g. boolean).
Table is small (<1000 rows — full scan is fine).
Table is write-heavy and queried rarely.
Column is rarely used in filters.

9. Glossary (শব্দকোষ)

Term	Meaning	বাংলায়
Full table scan	Reading every row to find a match.	প্রতিটি row পড়ে পড়ে খোঁজা।
B-tree	Balanced tree, default index in most DBs.	Balanced tree, সব database-এর default index।
Hash index	O(1) equality lookup, no range support.	Equality-তে দ্রুত, range-এ অকেজো।
Composite index	Index over multiple columns in order.	একাধিক কলাম মিলে index।
Covering index	Index containing all columns the query needs.	Query-এর সব কলাম index-এর ভেতরেই।
Partial index	Index only over rows matching a WHERE.	একটি WHERE-এ মেলে এমন row-এর উপর index।
Selectivity	Fraction of rows a filter eliminates.	একটি filter কত শতাংশ row বাদ দেয়।
Index-only scan	Query answered without touching the heap.	Heap না ছুঁয়েই query-এর answer।

10. Practice Problems

For each problem, run EXPLAIN QUERY PLAN first to confirm the index is being used.

প্রতিটি সমস্যায় answer খোলার আগে নিজে EXPLAIN QUERY PLAN চালিয়ে দেখুন — index actually কাজে আসছে কিনা।

Create an index on students(email) and verify with EXPLAIN QUERY PLAN.

students(email)-এ index বানান এবং EXPLAIN দিয়ে যাচাই করুন।
✨ Show Answer
ans1.sql
```
CREATE INDEX idx_email ON students(email);
EXPLAIN QUERY PLAN
SELECT * FROM students WHERE email = 'n@x.bd';
```
Why is an index on a boolean column (e.g. is_active) usually a bad idea?

is_active-এর মতো boolean কলামে index কেন প্রায়ই কাজে আসে না?

✨ Show Answer

Answer: A boolean has only two values, so any filter returns ~50% of rows. The index lookup costs more than the full scan it would replace, because the engine still needs to fetch half the heap pages anyway. Indexes pay off only when they eliminate a large fraction of rows.

Boolean কলামে মাত্র দুটি মান, তাই filter ~৫০% row ফিরিয়ে দেয়। সেক্ষেত্রে index lookup full scan-এর চেয়ে দামি — selectivity কম হলে index লাভজনক না।
Build an orders table and add a composite index (customer_id, status). Show that WHERE status = 'paid' alone does not use it.

orders-এ (customer_id, status) composite index বানান, এবং দেখান শুধু status filter index ব্যবহার করে না।
✨ Show Answer
ans3.sql
```
CREATE INDEX idx_cs ON orders(customer_id, status);
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid';
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 3;
```
Create a partial index on a tickets table that covers only status = 'open' rows.

tickets-এ শুধু open status-এর জন্য partial index বানান।
✨ Show Answer
ans4.sql
```
CREATE INDEX idx_open_owner ON tickets(owner_id) WHERE status = 'open';
EXPLAIN QUERY PLAN
SELECT * FROM tickets WHERE status='open' AND owner_id=1;
```
Show a covering-index plan for "SELECT name FROM users WHERE name LIKE 'Ar%'".

name LIKE 'Ar%' query-এর জন্য covering index plan দেখান।
✨ Show Answer
ans5.sql
```
EXPLAIN QUERY PLAN
SELECT name FROM users WHERE name LIKE 'Ar%';
```
Why does WHERE name LIKE '%arif' not use an index, while WHERE name LIKE 'arif%' does?

'arif%' index ব্যবহার করে কিন্তু '%arif' করে না — কেন?

✨ Show Answer

Answer: A B-tree is sorted by the leading characters of the key. 'arif%' is an anchored prefix — the engine can binary-search to "arif" and read forward. But '%arif' needs to consider every value (because the prefix is unknown), forcing a full scan.

B-tree-এ key-এর শুরু দিয়ে sort। 'arif%' মানে শুরু "arif" — সরাসরি লাফিয়ে যাওয়া যায়। কিন্তু '%arif' মানে শেষ "arif", শুরুটা যা-কিছু — তাই সব row দেখতে হয়।
Create an index that lets ORDER BY created_at DESC be served without a sort.

ORDER BY created_at DESC কে temp-sort ছাড়া serve করতে index বানান।
✨ Show Answer
ans7.sql
```
CREATE INDEX idx_created_desc ON posts(created_at DESC);
EXPLAIN QUERY PLAN
SELECT id, created_at FROM posts ORDER BY created_at DESC;
```
Explain the difference between a B-tree index and a hash index in two sentences.

B-tree আর hash index — দুটির পার্থক্য দুই বাক্যে।

✨ Show Answer

Answer: A B-tree keeps keys sorted in a balanced tree, supporting both equality and range queries (and ORDER BY for free). A hash index stores keys by their hash, giving O(1) equality lookup but no support for range queries or ordering.

B-tree balanced tree-এ key-গুলো sorted রাখে — equality, range, ORDER BY সব support করে। Hash index hash দিয়ে রাখে — equality দ্রুত, কিন্তু range বা order-এ অকেজো।
Why would a database refuse to use an existing index even when the column appears in WHERE?

Index থাকা সত্ত্বেও database কেন কখনো সেটি ব্যবহার করে না?

✨ Show Answer

Answer: When the planner estimates that the filter is not selective enough — say it would return 60% of the rows — the cost of jumping into the index, then the heap, for each row exceeds the cost of a full scan. The engine picks the cheaper plan based on its statistics. Common fixes: rerun ANALYZE, rewrite the query, or add a covering / partial index.

Planner হিসাব করে — যদি filter বেশিরভাগ row ফিরিয়ে দেয়, তাহলে index লাফালাফি না করে full scan-ই সস্তা। ANALYZE চালান, query পুনর্লেখুন, অথবা covering / partial index বিবেচনা করুন।
Demonstrate that adding many indexes to a table slows INSERT.

অনেক index INSERT-কে ধীর করে — দেখান (conceptually)।
✨ Show Answer
ans10.sql
```
-- Each INSERT touches 5 indexes besides the table:
INSERT INTO big(a,b,c,d,e) VALUES(1,2,3,4,5);
SELECT count(*) AS indexes_on_big FROM sqlite_master
WHERE type='index' AND tbl_name='big';
```
Drop an index and re-run EXPLAIN to confirm fallback to a scan.

Index drop করে EXPLAIN চালিয়ে scan-এ ফিরে যাওয়া দেখান।
✨ Show Answer
ans11.sql
```
EXPLAIN QUERY PLAN SELECT * FROM t WHERE name='b';
DROP INDEX idx_t_name;
EXPLAIN QUERY PLAN SELECT * FROM t WHERE name='b';
```
An e-commerce app frequently runs SELECT * FROM orders WHERE customer_id = ? ORDER BY created_at DESC LIMIT 10. Suggest the best single index.

এই query-এর জন্য সবচেয়ে উপযুক্ত একটি index suggest করুন।

✨ Show Answer

Answer: CREATE INDEX idx_customer_created ON orders(customer_id, created_at DESC); — leading column matches the filter, second column matches the sort direction, so SQLite can return the first 10 rows directly from the index without a separate sort.

প্রথমে customer_id দিয়ে filter, পরে created_at DESC দিয়ে sort — তাই (customer_id, created_at DESC) index ১০টি row সরাসরি দিতে পারবে, আলাদা sort লাগবে না।
Build a covering index for the query SELECT id, customer_id, total FROM orders WHERE status = 'pending'.

এই query-এর জন্য covering index বানান।
✨ Show Answer
ans13.sql
```
CREATE INDEX idx_status_cov ON orders(status, customer_id, total);
EXPLAIN QUERY PLAN
SELECT id, customer_id, total FROM orders WHERE status='pending';
```
Why is WHERE LOWER(email) = 'arif@x.bd' bad for an index on email?

WHERE LOWER(email)=... — index কাজে আসবে না কেন?

✨ Show Answer

Answer: The index stores the original values, not LOWER(email). The engine cannot use the index to find LOWER(...) values without recomputing for every row. Fix: either store emails already lowercased, or create a functional/expression index CREATE INDEX idx_email_lower ON users(LOWER(email));.

Index-এ original মান আছে, LOWER(email) নয় — তাই function applied হলে index অকেজো। সমাধান: email আগে থেকেই lowercase রাখুন, অথবা expression index বানান।
Show a query plan that uses a temporary B-tree for ORDER BY, then fix it with an index.

ORDER BY-তে temp B-tree ব্যবহার হচ্ছে — দেখান, এবং index দিয়ে সমাধান করুন।
✨ Show Answer
ans15.sql
```
EXPLAIN QUERY PLAN
SELECT * FROM products ORDER BY price;
CREATE INDEX idx_price ON products(price);
EXPLAIN QUERY PLAN
SELECT * FROM products ORDER BY price;
```
Three teammates suggest three different indexes for the same slow query. How do you decide?

তিনজন teammate তিনটা ভিন্ন index suggest করছে — কিভাবে decide করবেন?

✨ Show Answer

Answer: (1) Profile each candidate with EXPLAIN ANALYZE (Postgres) or EXPLAIN QUERY PLAN (SQLite) on production-like data. (2) Look at the read/write ratio of the table — an extra index hurts every write. (3) Prefer the index that is also useful for other queries in the workload, not just this one. Decisions should be data-driven, not intuition-driven.

প্রতিটি index প্রস্তাব production-এর মতো ডেটায় EXPLAIN দিয়ে যাচাই করুন; write/read ratio দেখুন; এবং যে index একসাথে একাধিক query-কে সাহায্য করে সেটি বেছে নিন। Intuition নয়, ডেটা দিয়ে সিদ্ধান্ত নিন।

Summary — Module 23

Indexes are the single biggest lever for query speed — and the single biggest source of write amplification. The default everywhere is the B-tree, which serves equality, range, and ordered queries. PostgreSQL adds hash indexes for pure equality. Composite indexes follow the leftmost-prefix rule: column order matters. Covering indexes answer queries without touching the heap. Partial indexes save space on skewed data. And EXPLAIN QUERY PLAN is your truth-teller — always check the plan before guessing.

Index query-গতি বাড়ানোর সবচেয়ে শক্তিশালী হাতিয়ার, কিন্তু write-cost বাড়ায়। Default সবখানে B-tree; Postgres-এ hash-ও আছে। Composite index-এ কলামের ক্রম গুরুত্বপূর্ণ; covering index heap না ছুঁয়েই query শেষ করে; partial index একটি subset-এর উপর। সবসময় EXPLAIN QUERY PLAN চালিয়ে নিশ্চিত হোন।

Next Module → Mid-term Project — একটি বাস্তব schema design।

Previous (Module 22) Next: Mid-term Project

1. Why Indexes Exist — and What They Cost

2. The B-Tree — The Default Index Everywhere

3. Hash Indexes — Postgres Has Them, SQLite Does Not

4. Composite Indexes — Column Order Matters

5. Covering Indexes — Index-Only Scans

6. Partial Indexes — Index Only the Rows You Care About

7. Reading EXPLAIN QUERY PLAN

8. When NOT to Add an Index

✅ Add an index (যোগ করুন)

⚠️ Skip the index (এড়িয়ে যান)

9. Glossary (শব্দকোষ)

10. Practice Problems

Summary — Module 23

7. Reading `EXPLAIN QUERY PLAN`