Composite Index কী?

Composite Index (বা Multi-Column Index) হলো একটি Index যা একাধিক কলামের উপর একসাথে তৈরি করা হয়। যখন Query-তে একাধিক কলাম একসাথে ব্যবহার হয়, তখন Composite Index Single-Column Index-এর চেয়ে অনেক বেশি কার্যকর হয়।

Composite Index কেন দরকার?

ধরুন আপনার একটি orders টেবিল আছে এবং আপনি প্রায়ই এই Query করেন:

SELECT * FROM orders
WHERE customer_id = 100 AND status = 'PENDING';

Single Index ব্যবহার করলে:

• customer_id Index ব্যবহার করে ১০০ জনের অর্ডার ফিল্টার করলো
• তারপর সেই ১০০টি রেকর্ড থেকে status = 'PENDING' খুঁজলো
• দুটি ধাপে কাজ হলো

Composite Index ব্যবহার করলে:

• (customer_id, status) Index সরাসরি সঠিক রেকর্ডে পৌঁছে
• একটি ধাপেই কাজ শেষ — অনেক দ্রুত!

Composite Index তৈরি করা

SQL-এ

-- customer_id এবং status কলামে Composite Index
CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

-- তিনটি কলামে Composite Index
CREATE INDEX idx_emp_dept_role_salary
ON employees(department_id, role, salary);

-- Unique Composite Index
CREATE UNIQUE INDEX idx_user_email_tenant
ON users(email, tenant_id);

Spring Boot JPA-তে

@Entity
@Table(
    name = "orders",
    indexes = {
        @Index(
            name = "idx_orders_customer_status",
            columnList = "customer_id, status"
        ),
        @Index(
            name = "idx_orders_customer_created",
            columnList = "customer_id, created_at"
        )
    }
)
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "customer_id")
    private Long customerId;

    @Column(name = "status")
    private String status;

    @Column(name = "created_at")
    private LocalDateTime createdAt;

    @Column(name = "total_price")
    private Double totalPrice;
}

Leftmost Prefix Rule (সবচেয়ে গুরুত্বপূর্ণ নিয়ম)

Composite Index (A, B, C) তৈরি করলে এটি আসলে তিনটি Index-এর সুবিধা দেয়: (A), (A, B), (A, B, C) — কিন্তু (B), (C), (B, C) এর জন্য কাজ করে না।

-- Index: (department_id, role, salary)
CREATE INDEX idx_emp_dept_role_salary ON employees(department_id, role, salary);

-- ✅ Index ব্যবহার করবে
WHERE department_id = 1
WHERE department_id = 1 AND role = 'DEVELOPER'
WHERE department_id = 1 AND role = 'DEVELOPER' AND salary > 50000
WHERE department_id = 1 ORDER BY role
WHERE department_id = 1 AND role = 'DEVELOPER' ORDER BY salary

-- ❌ Index ব্যবহার করবে না (leading column বাদ দিয়ে)
WHERE role = 'DEVELOPER'
WHERE salary > 50000
WHERE role = 'DEVELOPER' AND salary > 50000

Column Order নির্বাচনের কৌশল

নিয়ম ১: Equality আগে, Range পরে

-- ❌ ভুল ক্রম
CREATE INDEX idx_wrong ON orders(created_at, customer_id);
-- created_at range condition হওয়ায় পরের কলাম ব্যবহার হবে না

-- ✅ সঠিক ক্রম
CREATE INDEX idx_correct ON orders(customer_id, created_at);
-- customer_id = 100 (equality) → তারপর created_at range
SELECT * FROM orders WHERE customer_id = 100 AND created_at > '2024-01-01';

নিয়ম ২: High Cardinality কলাম আগে

-- email = unique values (high cardinality)
-- status = 'ACTIVE'/'INACTIVE' (low cardinality)

-- ✅ সঠিক ক্রম
CREATE INDEX idx_user ON users(email, status);
-- email দিয়ে দ্রুত ফিল্টার হয়, তারপর status

নিয়ম ৩: সবচেয়ে বেশি WHERE-এ ব্যবহৃত কলাম আগে

-- যদি সবচেয়ে বেশি ব্যবহৃত query হয়:
-- WHERE department_id = X (90% query)
-- WHERE department_id = X AND status = Y (60% query)

-- তাহলে:
CREATE INDEX idx_emp ON employees(department_id, status);

Covering Index — Index-থেকেই ডেটা নেওয়া

যদি SELECT-এ উল্লিখিত সব কলাম Index-এ থাকে, তাহলে Table-এ যেতেই হয় না। এটাকে Covering Index বলে।

-- এই Index তৈরি করুন
CREATE INDEX idx_orders_covering
ON orders(customer_id, status, created_at, total_price);

-- এই Query সম্পূর্ণ Index থেকেই পাবে, Table access নেই!
SELECT customer_id, status, created_at, total_price
FROM orders
WHERE customer_id = 100 AND status = 'COMPLETED';

-- EXPLAIN Output: "Using index" (Table access নেই!)

বাস্তব উদাহরণ — E-commerce Order System

@Entity
@Table(
    name = "orders",
    indexes = {
        // সবচেয়ে সাধারণ query pattern
        @Index(name = "idx_orders_cust_status", columnList = "customer_id, status"),
        // Date range query
        @Index(name = "idx_orders_cust_created", columnList = "customer_id, created_at"),
        // Admin panel query
        @Index(name = "idx_orders_status_created", columnList = "status, created_at"),
        // Covering index for list view
        @Index(name = "idx_orders_covering",
               columnList = "customer_id, status, created_at, total_price")
    }
)
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "customer_id", nullable = false)
    private Long customerId;

    @Column(name = "status")
    private String status;

    @Column(name = "created_at")
    private LocalDateTime createdAt;

    @Column(name = "total_price")
    private Double totalPrice;
}

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    // idx_orders_cust_status ব্যবহার করবে
    List<Order> findByCustomerIdAndStatus(Long customerId, String status);

    // idx_orders_cust_created ব্যবহার করবে
    List<Order> findByCustomerIdAndCreatedAtAfter(Long customerId, LocalDateTime date);

    // Covering index ব্যবহার করবে
    @Query("SELECT o.customerId, o.status, o.createdAt, o.totalPrice " +
           "FROM Order o WHERE o.customerId = :cid AND o.status = :status")
    List<Object[]> findSummaryByCustomerAndStatus(
        @Param("cid") Long customerId,
        @Param("status") String status
    );
}

সংক্ষেপে

উপসংহার

Composite Index সঠিকভাবে ব্যবহার করলে Multi-Column Query অনেক গুণ দ্রুত হয়। সবচেয়ে গুরুত্বপূর্ণ হলো Leftmost Prefix Rule মেনে কলামের ক্রম নির্ধারণ করা। Covering Index ব্যবহার করে Table Access সম্পূর্ণ এড়ানো সম্ভব, যা Performance আরও বাড়ায়। Query Pattern বিশ্লেষণ করে সঠিক Composite Index ডিজাইন করুন।