Making Non-Row-Key Queries Fast in Apache Phoenix with Global Secondary Indexes

Phoenix is fast exactly when your WHERE clause hits the row key. Filter on any other column and Phoenix scans the entire data table, region by region. The standard fix is a global secondary index: a separate HBase table that Phoenix maintains automatically and reads instead of your data table when the query shape fits. This piece shows how that index is structured, a worked example you can verify with EXPLAIN, and the write-path costs you pay for it.

What a global index actually is

When you create a global secondary index, Phoenix creates a new HBase table whose row key is the indexed column value plus the data table's row key. That design is what makes lookups fast: a query filtering on the indexed column becomes a range scan (or point get) over the index table, and the embedded data row key lets Phoenix jump straight back to the original row if it needs columns that aren't in the index.

"Global" means the index covers the whole table and lives independently of the data table's region layout — unlike a local index, which is co-located with each data region. Global indexes favor read performance; local indexes favor write performance. For the common case of read-heavy lookups by attribute, global is the usual choice.

A worked example

Assume a users table where the row key is an internal ID, but the application frequently looks users up by email:

CREATE TABLE users (
  user_id   BIGINT NOT NULL PRIMARY KEY,
  email     VARCHAR,
  name      VARCHAR,
  created_at TIMESTAMP
);

CREATE INDEX idx_users_email ON users (email) INCLUDE (name);

Run these in sqlline.py (the Phoenix CLI) or through the JDBC driver; DDL requires the same permissions as table creation on the cluster. The INCLUDE (name) clause copies the name column into every index row. That makes this a covered index for queries that select only email and name — Phoenix answers entirely from the index table and never touches the data table, which is the fastest configuration.

Now verify the optimizer actually uses it:

EXPLAIN SELECT name FROM users WHERE email = 'a@example.com';

You want the plan to show a scan over the index table (it appears as its own table name in the plan) rather than a full scan over USERS. If the plan still shows a full scan of the data table, common causes are: the index isn't in ACTIVE state yet, the query selects columns not in the index and Phoenix judged the double lookup too expensive, or statistics are missing (run UPDATE STATISTICS users;).

The write path: what each index costs you

Global mutable index maintenance is synchronous. Every UPSERT or DELETE on the data table also writes (or removes) the corresponding index entries before the write completes. Two consequences follow:

• Write amplification. One logical row write becomes one data write plus one index write per index. Three indexes means roughly 4x the HBase write traffic per row.
• Latency coupling. Your write now depends on the index table's regionservers being healthy. If the index table is unavailable, writes can fail or block depending on your index failure-handling settings, and reads may fall back to full data-table scans.

On a write-heavy table, measure before and after: time a bulk upsert batch, add the index, repeat. If ingest latency degrades beyond your budget, the index is the wrong tool — see the alternatives below.

Indexing an existing populated table

CREATE INDEX on a table that already has data only creates the index structure; it does not backfill existing rows. You must run the asynchronous index rebuild (the IndexTool MapReduce job) to populate it. Until that job finishes and the index transitions to ACTIVE, queries silently scan the data table — no error, just slow. Check index state in SYSTEM.CATALOG before concluding anything from query latency. The exact rebuild invocation and state names differ between Phoenix 4.x and 5.x, so follow the docs for your version.

Common mistakes

• Immutable vs mutable confusion. Immutable indexes are cheaper to maintain but are only correct for append-only tables. If rows are ever updated, an immutable index returns stale results — a correctness bug, not a performance issue. Default to mutable unless you're certain the table is write-once.
• Over-indexing. Each index is a real HBase table consuming storage, region count, and write bandwidth. Add indexes one at a time and measure.
• Non-covered queries. An index without INCLUDE columns still requires a lookup back to the data table for every matching row. For selective queries that's fine; for queries matching many rows it can be slower than a scan. Use EXPLAIN, not intuition.
• Indexing when a row-key redesign is better. If one access pattern dominates and ingest rate is very high, bake it into the row key instead — a composite key ((tenant_id, email)) or salting for hotspot control. Row-key scans have zero maintenance overhead. Indexes are for the secondary access patterns.

Quick decision check

Use a global covered index when: the query filters on a non-key column, the table is read-heavy or moderate-write, and you can include the selected columns. Skip it and redesign the row key when: ingest throughput is the hard constraint and the access pattern is known up front. In both cases, the verification loop is the same — EXPLAIN the query, confirm the plan touches the index table, and time your writes before and after.