Using Phoenix Secondary Indexes to Speed Non-Key Queries on HBase

When you query a Phoenix table using a column that isn’t part of the primary key, the engine falls back to a full table scan. This O(N) operation becomes a bottleneck as data grows.

Why secondary indexes help

Phoenix can maintain a separate HBase table that stores the indexed column values together with the primary key. The query planner can then resolve a WHERE clause on that column to an index scan, fetching only the matching rows.

Global versus local indexes

A global secondary index lives outside the base table’s region layout and can be used for any query that references the indexed column, provided the index covers the needed columns. A local secondary index is confined to each region server; it reduces write coordination but can only be used when the query also includes the same row‑key prefix that defines the table’s salted buckets.

Worked example: creating a global index

-- base table with a composite primary key
CREATE TABLE IF NOT EXISTS analytics.events (
  tenant_id VARCHAR NOT NULL,
  event_ts BIGINT NOT NULL,
  email VARCHAR,
  status VARCHAR,
  payload VARBINARY,
  CONSTRAINT pk PRIMARY KEY (tenant_id, event_ts)
);

-- global secondary index on the status column
CREATE INDEX idx_events_status ON analytics.events (status);

After the index is created, Phoenix builds an HBase table named something like analytics.events_idx_events_status. You can verify its existence in the HBase shell with:

list 'analytics.events.*'

To see whether the planner picks the index, run:

EXPLAIN SELECT tenant_id, event_ts, email FROM analytics.events WHERE status = 'FAILED';

The output should show an INDEX SCAN step rather than a FULL SCAN.

Trade‑offs and limitations

Each INSERT/UPSERT must also write to the index table, adding write amplification. In practice the extra latency is usually under 10 % but grows with the cardinality of the indexed column. Indexes are not automatically rebuilt after schema changes or region splits; you may need to run ALTER INDEX … REBUILD manually.

Actionable steps

1. Identify columns that are frequently filtered but not part of the primary key.
2. Create a global secondary index if you need unrestricted query patterns; otherwise consider a local index for write‑heavy workloads.
3. Validate the plan with EXPLAIN and measure latency before and after index creation.
4. Monitor write throughput and storage growth to ensure the overhead stays acceptable.