ইনডেক্স: কীভাবে কাজ করে আর কখন কাজে লাগে

শপের ছোট টেবিলগুলোতে প্রতিটা কোয়েরি চোখের পলকে শেষ, কারণ ১৪টা অর্ডার পড়তে SQLite-এর কোনো সময়ই লাগে না। কিন্তু আসল টেবিল ছোট থাকে না: আপনার অ্যাপের প্রতিটা LLM কলের লগ, রিকমেন্ডারকে খাওয়ানো ক্লিকস্ট্রিম, RAG-এর চাঙ্কের টেবিল — এগুলো খুব তাড়াতাড়ি লাখ লাখ সারিতে পৌঁছে যায়। কোনো সাহায্য ছাড়া "কাস্টমার ৪২-এর ইভেন্টগুলো দাও" প্রশ্নের উত্তর দিতে ডেটাবেস প্রতিটা সারি পড়ে। ইনডেক্স (index) হলো সেই সাহায্য: আলাদা, সাজানো একটা কাঠামো, যার মাধ্যমে ডেটাবেস দরকারি সারিগুলোতে সরাসরি পৌঁছে যায়।

এই পাতায় ২ লাখ সারির একটা টেবিল বানিয়ে আপনি নিজেই মেপে দেখবেন, ইনডেক্স আসলে কী বদলায় — স্টপওয়াচ দিয়ে নয় (সময় এক মেশিন থেকে আরেক মেশিনে বদলায়), বরং কোয়েরি প্ল্যান আর SQLite সত্যিই কতটা কাজ করে তা গুনে।

যা শিখবেন

• ইনডেক্স কী, আর প্রায় সব ইনডেক্সের পেছনে থাকা B-tree-র ধারণা
• প্রাইমারি বনাম সেকেন্ডারি ইনডেক্স, আর ইউনিক ইনডেক্স
• কম্পোজিট ইনডেক্স, কলামের ক্রম কেন জরুরি (leftmost-prefix নিয়ম), আর কভারিং ইনডেক্স
• সিলেক্টিভিটি আর কার্ডিনালিটি: ইনডেক্স কখন কাজে লাগে, কখন কিছুই করে না, আর এর খরচ কত
• ইনডেক্স যোগ করার আগে ও পরে EXPLAIN QUERY PLAN কীভাবে পড়বেন

পার্থক্য বোঝার মতো বড় একটা টেবিল

ধরুন শপটা প্রতিটা প্রোডাক্ট দেখা, কার্টে যোগ করা আর কেনাকাটা রেকর্ড করে — রিকমেন্ডার বা চার্ন মডেলের কাঁচামাল ঠিক এটাই। নিচের ব্লকটা একটা রিকার্সিভ CTE দিয়ে এমন ২ লাখ ইভেন্ট বানায় (দেখুন CTE আর রিকার্সিভ কোয়েরি)। সারির নম্বর i-এর ওপর কিছু পাটিগণিত মানগুলো ছড়িয়ে দেয়, তাই যতবারই চালান, ডেটা একই থাকে:

CREATE TABLE events (
    event_id    INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    event_type  TEXT    NOT NULL,
    product_id  INTEGER NOT NULL,
    event_day   TEXT    NOT NULL,
    amount      INTEGER NOT NULL
);

INSERT INTO events (event_id, customer_id, event_type, product_id, event_day, amount)
WITH RECURSIVE n(i) AS (
    SELECT 1
    UNION ALL
    SELECT i + 1 FROM n WHERE i < 200000
)
SELECT i,
       (i * 7919) % 5000 + 1,                       -- ৫,০০০ কাস্টমার
       CASE WHEN i % 20 = 0 THEN 'purchase'         -- ৫% কেনাকাটা
            WHEN i % 5 = 0 THEN 'cart'              -- ১৫% কার্টে যোগ
            ELSE 'view' END,                        -- ৮০% দেখা
       (i * 31) % 11 + 1,                           -- প্রোডাক্ট ১ থেকে ১১
       date('2026-01-01', '+' || (i % 181) || ' days'),
       (i * 37) % 5000 + 100
FROM n;

SELECT COUNT(*) AS events,
       COUNT(DISTINCT customer_id) AS customers,
       MIN(event_day) AS first_day,
       MAX(event_day) AS last_day
FROM events;

+--------+-----------+------------+------------+
| events | customers | first_day  | last_day   |
+--------+-----------+------------+------------+
| 200000 |      5000 | 2026-01-01 | 2026-06-30 |
+--------+-----------+------------+------------+

আগে: পুরো টেবিল স্ক্যান

কোয়েরির সামনে EXPLAIN QUERY PLAN লিখলে SQLite কোয়েরিটা না চালিয়েই বলে দেয়, সে এটা কীভাবে চালাত:

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE customer_id = 42;

+----+--------+---------+-------------+
| id | parent | notused | detail      |
+----+--------+---------+-------------+
|  2 |      0 |       0 | SCAN events |
+----+--------+---------+-------------+

SCAN events মানে: পুরো টেবিলটা সারি ধরে ধরে পড়া, আর প্রতিটা সারি শর্তের সাথে মিলিয়ে দেখা। পড়ার মতো অংশ হলো detail কলাম; বাকি তিনটা বলে প্ল্যানের ধাপগুলো কীভাবে একটার ভেতরে আরেকটা বসানো। স্ক্যানের খরচ দেখতে কয়েকটা ছোট হেল্পার লিখুন। plan() শুধু detail লাইনগুলো প্রিন্ট করে। count_steps() একটা স্টেটমেন্ট চালায় আর SQLite-এর বাইটকোড ধাপ গোনে: SQLite প্রতিটা স্টেটমেন্টকে একটা ছোট প্রোগ্রামে কম্পাইল করে, আর একটা progress handler দিয়ে Python সেই প্রোগ্রামের নির্দেশগুলো গুনতে পারে। work() ফলাফলটা প্রিন্ট করে। সময়ের মতো নয়, এই গোনা প্রতিবার একই আসে, তাই তুলনার জন্য এটা ন্যায্য মাপ। (এটা CPU-র কাজ মাপে, ডিস্ক থেকে পড়া নয় — নিচে কথাটা মনে রাখবেন।)

import sqlite3

from sqlhelp import con


def plan(sql, params=()):
    """একটা কোয়েরির জন্য SQLite-এর প্ল্যানের detail লাইনগুলো প্রিন্ট করে।"""
    fresh = sqlite3.connect("shop.db")      # নতুন কানেকশন সবসময় সর্বশেষ ইনডেক্সগুলো দেখে
    for row in fresh.execute("EXPLAIN QUERY PLAN " + sql, params):
        print(row[3])
    fresh.close()


def count_steps(sql, params=()):
    """একটা স্টেটমেন্ট চালায়; ফেরত দেয় (কয়টা সারি এল, SQLite কয়টা বাইটকোড ধাপ চালাল)।"""
    steps = 0

    def tick():
        nonlocal steps
        steps += 1
        return 0                      # 0 ফেরত দিলে SQLite চালিয়ে যায়

    con.set_progress_handler(tick, 1)  # প্রতিটা নির্দেশের পর tick() ডাকা হবে
    rows = con.execute(sql, params).fetchall()
    con.set_progress_handler(None, 1)
    return len(rows), steps


def work(sql, params=()):
    rows, steps = count_steps(sql, params)
    print(f"{rows} rows, {steps:,} steps")


query = "SELECT * FROM events WHERE customer_id = ?"
plan(query, (42,))
work(query, (42,))

SCAN events
40 rows, 600,372 steps

plan()-এ নতুন কানেকশন কেন? Python-এর sqlite3 প্রস্তুত করা স্টেটমেন্ট ক্যাশ করে রাখে, আর ক্যাশ করা EXPLAIN কখনো নতুন করে প্ল্যান হয় না — অন্য কোনো টুল (বা অন্য কানেকশন) দিয়ে ইনডেক্স যোগ করার পরও সে পুরোনো প্ল্যানই দেখাতে থাকবে। আসল কোয়েরিগুলো নিজে থেকেই নতুন করে প্ল্যান হয়; বাসি হয় শুধু প্ল্যানের প্রদর্শন।

মাত্র ৪০টা সারির জন্য প্রায় ৬ লাখ ধাপ — টেবিলের প্রতিটা সারির জন্য মোটামুটি তিনটা করে। টেবিল দ্বিগুণ হলে খরচও দ্বিগুণ: স্ক্যান হলো O(n)।

ইনডেক্স কী: B-tree-র ধারণা

পাঠ্যবইয়ের শেষে যে নির্ঘণ্ট (ইনডেক্স) থাকে, সেটার কথা ভাবুন। এন্ট্রিগুলো সাজানো, তাই "normalization" কয়েক সেকেন্ডে খুঁজে পান, আর প্রতিটা এন্ট্রি পুরো লেখা না দিয়ে শুধু পাতার নম্বর দেয়। ডেটাবেস ইনডেক্সও তাই: এক বা একাধিক কলামের একটা সাজানো কপি, যার প্রতিটা এন্ট্রি তার সারির দিকে নির্দেশ করে। প্রায় সবগুলোই B-tree — ভারসাম্যপূর্ণ গাছ, যার প্রতিটা নোডে অনেকগুলো key থাকে, তাই বিশাল টেবিলেও মাত্র কয়েকটা স্তর লাগে:

                     index on customer_id (sorted)
                  ┌──────────────────────────────┐
                  │  root:   1250 | 2500 | 3750  │
                  └──────┬────────┬───────┬──────┘
             ┌───────────┘        │       └────────────┐
     ┌───────▼────────┐   ┌───────▼────────┐   ┌───────▼────────┐
     │ 1 .. 1249      │   │ 1250 .. 2499   │   │ ...            │  inner nodes
     └───────┬────────┘   └────────────────┘   └────────────────┘
     ┌───────▼─────────────────────────────┐
     │ leaf: (42, row 4839) (42, row 9839) │  key + pointer to the row
     │       (42, row 14839) ...           │
     └─────────────────────────────────────┘

একটা key খোঁজা মানে root থেকে নেমে একটা লিফ নোডে (leaf) পৌঁছানো: ২ লাখ তুলনার বদলে মোটামুটি log₂(200,000) ≈ ১৮টা তুলনা। লিফগুলো ক্রমানুসারে সাজানো বলে ইনডেক্স করা কলামে রেঞ্জ (BETWEEN, >=) আর ORDER BY-ও সস্তা হয়ে যায়। এবার একটা ইনডেক্স বানান:

CREATE INDEX idx_events_customer ON events (customer_id);

plan(query, (42,))
work(query, (42,))

SEARCH events USING INDEX idx_events_customer (customer_id=?)
40 rows, 504 steps

একই ৪০টা সারি, কাজ প্রায় ১,২০০ গুণ কম। প্ল্যান SCAN থেকে বদলে হয়েছে SEARCH … USING INDEX: SQLite ইনডেক্সে ৪২ খুঁজেছে, তারপর টেবিল থেকে শুধু ওই ৪০টা সারি এনেছে। কোয়েরিতে আপনি কিছুই বদলাননি; প্ল্যানার নিজেই ইনডেক্সটা বেছে নিয়েছে। ইনডেক্সের মূল কথাই এটা — ফলাফল না বদলে কোয়েরিকে দ্রুত করা।

প্রাইমারি, সেকেন্ডারি আর ইউনিক ইনডেক্স

টেবিলটা নিজেই তার key অনুযায়ী সাজানো একটা B-tree হিসেবে রাখা থাকে। SQLite-এ INTEGER PRIMARY KEY-ই সেই key (rowid), তাই প্রাইমারি কি দিয়ে খুঁজতে কোনো বাড়তি ইনডেক্সই লাগে না:

plan("SELECT * FROM events WHERE event_id = ?", (4242,))
work("SELECT * FROM events WHERE event_id = ?", (4242,))

SEARCH events USING INTEGER PRIMARY KEY (rowid=?)
1 rows, 14 steps

বাকি সব ইনডেক্স হলো সেকেন্ডারি ইনডেক্স: আলাদা একটা B-tree, যার এন্ট্রিগুলো টেবিলের দিকে নির্দেশ করে। "নির্দেশ করা" বলতে কী বোঝায়, তা ডেটাবেসভেদে আলাদা:

ইউনিক ইনডেক্স একটা নিয়মও চাপিয়ে দেয়: দুটো এন্ট্রি সমান হতে পারবে না। প্রতিটা UNIQUE কনস্ট্রেইন্ট, আর SQLite-এর INTEGER PRIMARY KEY (যেটা নিজেই rowid) বাদে প্রতিটা PRIMARY KEY, আসলে এমন একটা ইনডেক্স দিয়েই চলে — এভাবেই SQLite এত দ্রুত customers.email যাচাই করে:

PRAGMA index_list('customers');

+-----+------------------------------+--------+--------+---------+
| seq | name                         | unique | origin | partial |
+-----+------------------------------+--------+--------+---------+
|   0 | sqlite_autoindex_customers_1 |      1 | u      |       0 |
+-----+------------------------------+--------+--------+---------+

origin = u মানে এটা এসেছে একটা UNIQUE কনস্ট্রেইন্ট থেকে। নিজেও একটা বানাতে পারেন, আর তখন সেটা কনস্ট্রেইন্টের মতোই ডুপ্লিকেট আটকায়:

CREATE UNIQUE INDEX idx_products_name ON products (name);
INSERT INTO products (product_id, name, category_id, price, stock)
VALUES (12, 'Wireless Mouse', 2, 950, 10);

Error: UNIQUE constraint failed: products.name

কম্পোজিট ইনডেক্স আর leftmost-prefix নিয়ম

কম্পোজিট ইনডেক্স কয়েকটা কলাম নিয়ে গড়া: প্রথমে প্রথম কলাম অনুযায়ী সাজানো, তারপর প্রথম কলামের প্রতিটা মানের ভেতরে দ্বিতীয় কলাম অনুযায়ী — ঠিক যেমন টেলিফোন ডিরেক্টরি আগে পদবি, তারপর নাম অনুযায়ী সাজানো। এক কলামের ইনডেক্সটার জায়গায় (customer_id, event_day)-এর ওপর একটা ইনডেক্স বসান:

DROP INDEX idx_events_customer;
CREATE INDEX idx_events_customer_day ON events (customer_id, event_day);

checks = [
    ("SELECT * FROM events WHERE customer_id = ? AND event_day >= ?", (42, "2026-06-01")),
    ("SELECT * FROM events WHERE customer_id = ? ORDER BY event_day DESC LIMIT 3", (42,)),
    ("SELECT * FROM events WHERE customer_id = ? ORDER BY amount DESC LIMIT 3", (42,)),
    ("SELECT * FROM events WHERE event_day = ?", ("2026-06-01",)),
]
for sql, params in checks:
    print(sql)
    plan(sql, params)
    print()

SELECT * FROM events WHERE customer_id = ? AND event_day >= ?
SEARCH events USING INDEX idx_events_customer_day (customer_id=? AND event_day>?)

SELECT * FROM events WHERE customer_id = ? ORDER BY event_day DESC LIMIT 3
SEARCH events USING INDEX idx_events_customer_day (customer_id=?)

SELECT * FROM events WHERE customer_id = ? ORDER BY amount DESC LIMIT 3
SEARCH events USING INDEX idx_events_customer_day (customer_id=?)
USE TEMP B-TREE FOR ORDER BY

SELECT * FROM events WHERE event_day = ?
SCAN events

• দুই কলামেই ফিল্টার থাকলে দুটোই কাজে লাগে: customer_id-এ সমতা, তারপর তার ভেতরে event_day-এ রেঞ্জ।
• একজন কাস্টমারের জন্য ORDER BY event_day-এ আলাদা করে সাজাতে হয় না: ইনডেক্সে ওই কাস্টমারের দিনগুলো আগে থেকেই ক্রমানুসারে আছে। amount দিয়ে সাজাতে হলে লাগে: USE TEMP B-TREE FOR ORDER BY মানে SQLite সাজানোর জন্য একটা অস্থায়ী কাঠামো বানাচ্ছে।
• শুধু event_day দিয়ে ফিল্টার করলে ইনডেক্স কাজে লাগে না — পদবি অনুযায়ী সাজানো ডিরেক্টরিতে "রহিম" নামের সবাইকে খোঁজার মতো। ইনডেক্স কাজে লাগে শুধু তার কলামগুলোর একটা leftmost prefix-এর (বাঁ দিক থেকে শুরু হওয়া অংশের) জন্য।

কলামের ক্রমের সহজ নিয়ম: যেসব কলাম = দিয়ে তুলনা হয় সেগুলো আগে, রেঞ্জ বা সাজানোর কলাম শেষে। (প্রথম কলামে আলাদা মান কম থাকলে SQLite (পরিসংখ্যান থাকলে), MySQL 8.0.13+ আর PostgreSQL 18+ কখনো কখনো সেটা "skip-scan" করতে পারে, কিন্তু এর ভরসায় কখনো ইনডেক্স ডিজাইন করবেন না।)

কভারিং ইনডেক্স

ইনডেক্সের এন্ট্রিতে তার কলামগুলো আগে থেকেই থাকে। কোনো কোয়েরির যদি শুধু ইনডেক্সে থাকা কলামগুলোই লাগে, ডেটাবেসকে টেবিলে যেতেই হয় না — ইনডেক্সটা কোয়েরিকে কভার করে:

plan("SELECT event_day FROM events WHERE customer_id = ?", (42,))
plan("SELECT event_day, amount FROM events WHERE customer_id = ?", (42,))

SEARCH events USING COVERING INDEX idx_events_customer_day (customer_id=?)
SEARCH events USING INDEX idx_events_customer_day (customer_id=?)

amount-ও চাইলে টেবিলে ৪০ বার যাওয়া-আসা করতে হয়। ধাপ গুনে এই খরচ ধরা পড়ে না (টেবিলটা আগে থেকেই মেমরিতে আছে), কিন্তু মেমরিতে না আঁটা বড় টেবিলে প্রতিটা যাওয়া-আসা হতে পারে ডিস্কের এলোমেলো জায়গা থেকে পড়া। "দিনভিত্তিক একজন কাস্টমারের খরচ" যদি এমন কোয়েরি হয়, যা আপনার ফিচার পাইপলাইন লাখ লাখ বার চালায়, তাহলে amount-কেও ইনডেক্সে ঢোকান। তখন পুরোনো ইনডেক্সটা নতুনটার leftmost prefix হয়ে যায়, অর্থাৎ অপ্রয়োজনীয় (redundant) — ওটা ফেলে দিন:

CREATE INDEX idx_events_customer_day_amount ON events (customer_id, event_day, amount);
DROP INDEX idx_events_customer_day;

plan("SELECT event_day, amount FROM events WHERE customer_id = ?", (42,))

SEARCH events USING COVERING INDEX idx_events_customer_day_amount (customer_id=?)

অন্য ডেটাবেসেও ব্যাপারটা একই। MySQL-এর EXPLAIN কভার করা কোয়েরিতে দেখায় Using index। PostgreSQL একে বলে Index Only Scan, আর শুধু কভার করার জন্য ইনডেক্সে key নয় এমন কলামও যোগ করতে দেয়: CREATE INDEX … ON events (customer_id, event_day) INCLUDE (amount)। PostgreSQL-এ একটা শর্ত আছে: তার ইনডেক্স এন্ট্রিতে সারিটা কোন ট্রানজ্যাকশনের কাছে দৃশ্যমান, সেই তথ্য থাকে না। তাই index-only scan টেবিলে না গিয়ে পারে শুধু সেই পেজগুলোর বেলায়, যেগুলোকে visibility map "সবার কাছে দৃশ্যমান" বলে চিহ্নিত করেছে। সম্প্রতি অনেক বদল হওয়া টেবিলে VACUUM (বা autovacuum) চলার আগ পর্যন্ত তাকে টেবিলেও যেতে হয়।

সিলেক্টিভিটি আর কার্ডিনালিটি: ইনডেক্স কখন কাজে লাগে

একটা কলামের কার্ডিনালিটি (cardinality) হলো তাতে কতগুলো আলাদা মান আছে। সিলেক্টিভিটি (selectivity) হলো একটা মান টেবিলের কত অংশ সারি বেছে নেয়: অংশটা যত ছোট, ইনডেক্স তত বেশি কাজে লাগে।

SELECT event_type,
       COUNT(*) AS rows_matched,
       ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM events), 1) AS pct_of_table
FROM events
GROUP BY event_type
ORDER BY rows_matched;

+------------+--------------+--------------+
| event_type | rows_matched | pct_of_table |
+------------+--------------+--------------+
| purchase   |        10000 |          5.0 |
| cart       |        30000 |         15.0 |
| view       |       160000 |         80.0 |
+------------+--------------+--------------+

customer_id-এ ৫,০০০টা আলাদা মান (একজন কাস্টমার টেবিলের ০.০২%); event_type-এ মাত্র ৩টা। তবু এতে ইনডেক্স বানিয়ে প্রতিটা টাইপ ইনডেক্সসহ আর ইনডেক্স ছাড়া তুলনা করুন (NOT INDEXED লিখলে SQLite কোনো ইনডেক্স ব্যবহার করতে পারে না):

CREATE INDEX idx_events_type ON events (event_type);

for event_type in ("purchase", "view"):
    rows, with_index = count_steps(
        "SELECT SUM(amount) FROM events WHERE event_type = ?", (event_type,))
    rows, full_scan = count_steps(
        "SELECT SUM(amount) FROM events NOT INDEXED WHERE event_type = ?", (event_type,))
    print(f"{event_type:9} index: {with_index:>9,} steps   scan: {full_scan:>9,} steps")

purchase  index:    50,123 steps   scan:   620,012 steps
view      index:   800,013 steps   scan:   920,011 steps

কেনাকাটার (৫% সারি) বেলায় ইনডেক্স ৯০%-এর বেশি কাজ বাঁচায়। দেখার (৮০%) বেলায় প্রায় কিছুই বাঁচায় না — আর আসল ডিস্কে প্রায়ই উল্টো ধীর হয়, কারণ ১,৬০,০০০টা ইনডেক্স এন্ট্রি মানে ডিস্কে টেবিলের ছড়ানো-ছিটানো পেজে ১,৬০,০০০ বার লাফানো, অথচ স্ক্যান পেজগুলো পরপর পড়ে যায়। এজন্যই পরিসংখ্যান জানা প্ল্যানার (পরের পাতা) কম সিলেক্টিভ মানের ইনডেক্স প্রায়ই উপেক্ষা করে। যদি শুধু বিরল মানটা নিয়েই কোয়েরি করেন, তাহলে পার্শিয়াল ইনডেক্স (SQLite আর PostgreSQL) কেবল সেই সারিগুলোকেই ইনডেক্স করে:

DROP INDEX idx_events_type;
CREATE INDEX idx_purchases_customer ON events (customer_id) WHERE event_type = 'purchase';

plan("SELECT * FROM events WHERE event_type = 'purchase' AND customer_id = ?", (40,))

SEARCH events USING INDEX idx_purchases_customer (customer_id=?)

ইনডেক্সের খরচ

ইনডেক্স বিনা পয়সায় আসে না। এটা ডিস্কে জায়গা নেয়, আর প্রতিটা INSERT, ইনডেক্স করা কলামের প্রতিটা UPDATE আর প্রতিটা DELETE-এ প্রতিটা ইনডেক্সও হালনাগাদ করতে হয়। জায়গার হিসাব দেখায় dbstat ভার্চুয়াল টেবিল (এটা থাকে শুধু SQLite যদি এটা সহ কম্পাইল করা হয়, যেমন বেশিরভাগ Linux বিল্ডে; no such table: dbstat এলে বুঝবেন আপনার বিল্ডে এটা নেই):

from sqlhelp import show

show("""
SELECT name, COUNT(*) AS pages, SUM(pgsize) / 1024 AS kib
FROM dbstat
WHERE name LIKE '%events%' OR name LIKE 'idx_purchases%'
GROUP BY name
ORDER BY kib DESC
""")

+--------------------------------+-------+------+
| name                           | pages | kib  |
+--------------------------------+-------+------+
| events                         |  1576 | 6304 |
| idx_events_customer_day_amount |  1221 | 4884 |
| idx_purchases_customer         |    28 |  112 |
+--------------------------------+-------+------+

তিন কলামের কভারিং ইনডেক্সটা মূল টেবিলের তিন-চতুর্থাংশেরও বেশি জায়গা নিয়েছে; পার্শিয়াল ইনডেক্সটা ছোট্ট, কারণ তাতে আছে মাত্র ৫% সারি। এবার লেখার খরচ: একই ৫০,০০০ সারি একবার কোনো সেকেন্ডারি ইনডেক্স ছাড়া টেবিলে, আরেকবার তিনটা ইনডেক্সওয়ালা টেবিলে ঢোকান:

con.execute("CREATE TABLE events_plain AS SELECT * FROM events WHERE 0")      # একই কলাম, কোনো সারি নেই
con.execute("CREATE TABLE events_indexed AS SELECT * FROM events WHERE 0")
con.execute("CREATE INDEX idx_ei_customer ON events_indexed (customer_id, event_day, amount)")
con.execute("CREATE INDEX idx_ei_type ON events_indexed (event_type)")
con.execute("CREATE INDEX idx_ei_product ON events_indexed (product_id)")

for table in ("events_plain", "events_indexed"):
    _, steps = count_steps(f"INSERT INTO {table} SELECT * FROM events WHERE event_id <= 50000")
    con.commit()
    print(f"{table:15} {steps:>9,} steps")

events_plain      750,017 steps
events_indexed  1,500,020 steps

তিনটা ইনডেক্স একই লোডের খরচ দ্বিগুণ করে দিয়েছে। যে টেবিলে পড়ার চেয়ে লেখা অনেক বেশি হয় — কাঁচা ইভেন্ট বা লগের স্রোত — সেখানে প্রতিটা ইনডেক্সের পেছনে জোরালো কারণ থাকা চাই। আর যে টেবিল সারাদিন পড়া হয় — প্রোডাক্ট, ফিচার টেবিল, RAG চাঙ্কের মেটাডেটা — সেখানে যেসব কলাম দিয়ে ফিল্টার আর জয়েন করেন, সেগুলোর ইনডেক্সই সাধারণত পারফরম্যান্সের জন্য আপনার সেরা কাজ।

আসল সিস্টেমে ইনডেক্স

• ফিচার লুকআপ, API আর LLM লগ। "এই ইউজারের সব ইভেন্ট", "গত সপ্তাহে এই ইউজারের কলগুলো": যে কলাম দিয়ে ফিল্টার করেন তাতে ইনডেক্স দিন, অথবা (user_id, created_at)-এর মতো কম্পোজিট। SQLite আর PostgreSQL ফরেন কি-তে নিজে থেকে ইনডেক্স বানায় না — শপের orders.customer_id-এ কোনো ইনডেক্স নেই।
• RAG আর ভেক্টর সার্চ। চাঙ্ক টেবিলে document_id আর মেটাডেটা ফিল্টারের জন্য B-tree ইনডেক্স লাগে, সাথে pgvector-এর HNSW-এর মতো একটা ভেক্টর ইনডেক্স (দেখুন AI অ্যাপের জন্য SQL: এমবেডিং, pgvector আর RAG মেটাডেটা) — "সমান" নয়, "সবচেয়ে কাছের" খোঁজার জন্য বানানো আলাদা কাঠামো, কিন্তু টানাপোড়েন একই: দ্রুত পড়া, ধীর লেখা, বাড়তি জায়গা।
• পাহারাদার হিসেবে ইউনিকনেস। (source, external_id)-এ একটা ইউনিক ইনডেক্স থাকলে পাইপলাইন আবার চালালে ডেটা চুপচাপ দ্বিগুণ হয় না — হয় ব্যর্থ হয়, নয়তো upsert হয়।

সাধারণ ভুল

• "কাজে লাগতে পারে" ভেবে প্রতিটা কলামে ইনডেক্স। প্রতিটা ইনডেক্স প্রতিটা লেখাকে ধীর করে আর জায়গা খায়, অথচ বেশিরভাগ কখনো ব্যবহারই হয় না। যে কোয়েরিগুলো আসলে চালান, সেগুলোর জন্য ইনডেক্স দিন; PostgreSQL-এ pg_stat_user_indexes.idx_scan = 0 দিয়ে অব্যবহৃত ইনডেক্স খুঁজে পাবেন।
• কলামের ভুল ক্রম। (event_day, customer_id)-এর ওপর ইনডেক্স WHERE customer_id = 42-এ কোনো কাজে আসে না। যে সমতার কলামগুলো আপনার কোয়েরিতে সবসময় থাকে, সেগুলো আগে রাখুন: CREATE INDEX … ON events (customer_id, event_day)।
• অপ্রয়োজনীয় ইনডেক্স রেখে দেওয়া। (customer_id, event_day) থাকলে (customer_id) অকেজো; ছোটটা ফেলে দিন।
• কলামকে ফাংশনের ভেতরে লুকিয়ে ফেলা। ইনডেক্সে রাখা আছে event_day, substr(event_day, 1, 7) নয়, তাই এটা ইনডেক্সে মাস খুঁজতে পারে না। তার বদলে খালি কলামের ওপর রেঞ্জ লিখুন:
  Pythonকপি

  plan("SELECT COUNT(*) FROM events WHERE customer_id = 42 AND substr(event_day, 1, 7) = '2026-06'")
  plan("SELECT COUNT(*) FROM events WHERE customer_id = 42 AND event_day >= '2026-06-01' AND event_day < '2026-07-01'")

  আউটপুটকপি

  SEARCH events USING COVERING INDEX idx_events_customer_day_amount (customer_id=?)
  SEARCH events USING COVERING INDEX idx_events_customer_day_amount (customer_id=? AND event_day>? AND event_day<?)

  প্রথম প্ল্যান শুধু customer_id দিয়ে খোঁজে, তারপর ওই কাস্টমারের প্রতিটা দিন আলাদা করে যাচাই করে; দ্বিতীয়টা দুই কলাম দিয়েই খোঁজে। এমন sargable ফিল্টার নিয়ে বিস্তারিত পরের পাতায়।
• কম সিলেক্টিভ ফিল্টার ইনডেক্সে ঠিক হবে ভাবা। হ্যাঁ/না কলাম বা তিন মানের স্ট্যাটাসে ইনডেক্স কমই কাজে লাগে। বিরল মানের জন্য পার্শিয়াল ইনডেক্স দিন, অথবা এমন কম্পোজিট ইনডেক্স, যার শুরুতে একটা সিলেক্টিভ কলাম।

নিজে চেষ্টা করুন

1. সহজ: SELECT COUNT(*) FROM events WHERE product_id = 7-এর প্ল্যান দেখুন, এমন একটা ইনডেক্স যোগ করুন যাতে স্ক্যান সার্চে বদলে যায়, তারপর আবার প্ল্যান দেখুন।
2. মাঝারি: মার্কেটিং টিম প্রতি ঘণ্টায় চালায় SELECT customer_id, amount FROM events WHERE event_type = 'cart' AND event_day BETWEEN '2026-03-01' AND '2026-03-07'। এমন একটা ইনডেক্স ডিজাইন করুন, যাতে SQLite দুটো শর্ত দিয়েই খুঁজতে পারে আর টেবিলে একবারও না যায়; প্ল্যান দিয়ে প্রমাণ করুন।
3. কঠিন: একটা চার্ন মডেলের দরকার প্রতি কাস্টমারের মোট কেনাকাটার খরচ। এমন একটা পার্শিয়াল কভারিং ইনডেক্স বানান, যাতে SELECT customer_id, SUM(amount) FROM events WHERE event_type = 'purchase' GROUP BY customer_id শুধু ইনডেক্সটাই পড়ে, আর ইনডেক্সসহ ও ছাড়া (NOT INDEXED) ধাপ তুলনা করুন।

উত্তর

# ১. সহজ
plan("SELECT COUNT(*) FROM events WHERE product_id = 7")
con.execute("CREATE INDEX idx_events_product ON events (product_id)")
plan("SELECT COUNT(*) FROM events WHERE product_id = 7")

# ২. মাঝারি: সমতার কলাম আগে, রেঞ্জের কলাম তারপর, বেছে নেওয়া কলামগুলো শেষে
con.execute("CREATE INDEX idx_events_type_day ON events (event_type, event_day, customer_id, amount)")
plan("""SELECT customer_id, amount FROM events
        WHERE event_type = 'cart' AND event_day BETWEEN '2026-03-01' AND '2026-03-07'""")
# SEARCH events USING COVERING INDEX idx_events_type_day (event_type=? AND event_day>? AND event_day<?)

# ৩. কঠিন (আগে মাঝারির ইনডেক্সটা ফেলে দিন, ওটাও এই কোয়েরি কভার করত)
con.execute("DROP INDEX IF EXISTS idx_events_type_day")
sql = "SELECT customer_id, SUM(amount) FROM events {} WHERE event_type = 'purchase' GROUP BY customer_id"
con.execute("""CREATE INDEX idx_purchase_spend ON events (customer_id, amount)
               WHERE event_type = 'purchase'""")
plan(sql.format(""))
print(count_steps(sql.format("")))
print(count_steps(sql.format("NOT INDEXED")))

সারসংক্ষেপ

• ইনডেক্স হলো কিছু কলামের সাজানো কপি, যা সারিগুলোর দিকে নির্দেশ করে; B-tree পুরো টেবিল স্ক্যান না করে হাতে গোনা কয়েক ধাপে একটা key খুঁজে পায়।
• EXPLAIN QUERY PLAN দেখায় SCAN (সব পড়া) নাকি SEARCH … USING INDEX; COVERING INDEX মানে টেবিলে হাতই পড়েনি।
• কম্পোজিট ইনডেক্স কাজে লাগে শুধু তার কলামগুলোর leftmost prefix-এ: সমতার কলাম আগে, রেঞ্জ বা সাজানোর কলাম শেষে।
• সিলেক্টিভ ফিল্টারে ইনডেক্স দারুণ লাভ দেয়; টেবিলের বড় অংশ মেলে এমন মানে প্রায় কিছুই দেয় না। বিরল মানের জন্য পার্শিয়াল ইনডেক্স।
• প্রতিটা ইনডেক্স জায়গা খায় আর প্রতিটা লেখাকে ধীর করে — যে কোয়েরি চালান তার জন্যই ইনডেক্স দিন, আর অপ্রয়োজনীয়গুলো ফেলে দিন।

এরপর: EXPLAIN আর কোয়েরি অপ্টিমাইজেশন পাতায় SQLite, PostgreSQL আর MySQL-এর পূর্ণ কোয়েরি প্ল্যান পড়বেন, আর এই পাতার ধারণাগুলো দিয়ে ধীর কোয়েরি ঠিক করার একটা রুটিন বানাবেন: sargable ফিল্টার, ভালো জয়েন, keyset পেজিনেশন, Python থেকে N+1 কোয়েরি আর পরিসংখ্যান।