Reducing HBase Scan Latency with Apache Phoenix Secondary Indexes

The Full Table Scan Problem

In a standard HBase schema, data is retrieved efficiently only if you know the row key. If your query filters by any other column, HBase must perform a full table scan—reading every single row in the table to find those that match your criteria. For tables with millions of rows, this transforms a millisecond‑level lookup into a multi‑second (or minute) operation, consuming massive amounts of RegionServer I/O and memory.

The solution in Apache Phoenix is the Secondary Index. By creating an index on a non‑primary key column, you effectively create a hidden mapping table that allows Phoenix to perform a point lookup on the index first, then jump directly to the specific row in the base table.

How Phoenix Implements Indexing

When you define a secondary index, Phoenix creates a separate HBase table. This index table stores the value of the indexed column as its own row key, with the primary key of the base table as the value.

Phoenix uses a Query Optimizer to handle this automatically. When you execute a SELECT statement with a WHERE clause on an indexed column, the optimizer rewrites the query to hit the index table first. This reduces the search complexity from O(N), where N is the total number of rows, to O(log N), the cost of a B‑Tree lookup in HBase.

Local vs. Global Indexes

Phoenix offers two primary indexing strategies depending on your consistency and performance needs:

• Global Indexes: These are stored in a completely separate HBase table. They are highly efficient for reads because they can be queried independently of the base table's region distribution.
• Local Indexes: These store the index entries in the same region as the base data. While they may require scanning multiple regions, they offer better write performance and stronger consistency because the index update happens in the same atomic mutation as the data update.

Worked Example: Accelerating User Lookups

Consider a USERS table where the primary key is USER_ID, but the application frequently queries by EMAIL. Without an index, every email search is a full scan.

1. Create the Base Table

-- Run via Phoenix Query Server (sqlline)
CREATE TABLE USERS (
    USER_ID BIGINT NOT NULL PRIMARY KEY,
    USERNAME VARCHAR,
    EMAIL VARCHAR,
    CREATED_DATE DATE
);

2. Create the Secondary Index

To optimize email lookups, create an index on the EMAIL column. Ensure you have hbase:write permissions on the namespace.

CREATE INDEX IDX_USER_EMAIL ON USERS (EMAIL);

3. Verify the Execution Plan

Use the EXPLAIN keyword to verify that Phoenix is using the index rather than a full scan. Run this before and after creating the index:

EXPLAIN SELECT * FROM USERS WHERE EMAIL = 'tech-editor@example.com';

Expected Check: In the output, look for INDEX SCAN instead of FULL SCAN. If you see FULL SCAN, the optimizer has decided the index is not beneficial (common with very low‑cardinality columns).

Trade‑offs and Limitations

Indexing is not a "free" performance boost. Every index introduces a write penalty. When you INSERT, UPDATE, or DELETE a row in the base table, Phoenix must also update the corresponding entries in the index table. In write‑heavy environments, adding too many indexes can significantly throttle ingestion throughput.

Additionally, indexes are most effective on high‑cardinality columns (columns with many unique values, like emails or UUIDs). Creating an index on a low‑cardinality column (like GENDER or STATUS) often results in the optimizer ignoring the index because scanning the base table is faster than performing thousands of individual lookups via the index.

Practical Verification and Rollback

To test the impact on your specific dataset, measure the latency of a query using SELECT and compare it to the HBase RegionServer logs to see the number of scanned rows. If the write overhead becomes unsustainable, you can remove the index without affecting the base data:

-- Run via Phoenix Query Server
DROP INDEX IDX_USER_EMAIL ON USERS;

This operation removes the index table from HBase and restores write performance to the base table immediately.