Composite Indexes

What you'll learn

Index multiple fields together for compound filter queries.

When to use composite indexes

A composite index on [plan, country] accelerates queries that filter on BOTH fields simultaneously. An AND-of-eq filter on plan = "pro" AND country = "US" will hit the composite index instead of scanning.

Create a composite index

curl -s -X POST http://localhost:7475/v1/agents/users/indexes \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d '{
    "name":   "idx_plan_country",
    "fields": ["plan", "country"]
  }' | python3 -m json.tool

Query that uses the composite index

curl -s -X POST http://localhost:7475/v1/agents/users/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d '{
    "type": "scan",
    "limit": 50,
    "filter": {
      "AND": [
        {"field": "plan",    "op": "eq", "value": "pro"},
        {"field": "country", "op": "eq", "value": "US"}
      ]
    }
  }' | python3 -m json.tool

Rules for composite index activation

• ALL indexed fields must appear in an AND block with op: "eq"
• Order matters — the filter must match the index field order
• A partial match (only some fields) falls back to a single-field index if one exists

Challenge

Create a composite index on [category, status] for an orders agent. Write 30 orders with varying combinations. Query category = "electronics" AND status = "shipped" and verify via explain that the composite index is used.

---