LindormTable supports secondary indexes in the Tabular model. Secondary indexes let you query data on non-primary-key columns without changing your application's write logic—Lindorm handles index maintenance automatically and guarantees consistency between the primary table and all index tables.
Prerequisites
Before you begin, ensure that you have:
A Lindorm instance
A SQL client connected to LindormTable. See Using the LindormTable Engine with Lindorm Wide-Table SQL or Connect to and use LindormTable with Lindorm-cli
Key concepts
How indexes and primary tables relate
Each secondary index maps to an independent physical data table, separate from the primary table. You can create multiple indexes for a single primary table, including compound indexes built on one or more columns. When you write to the primary table, Lindorm automatically updates all associated index tables. When you query, specify conditions in the WHERE clause against the primary table—Lindorm selects the best index automatically, including falling back to the primary table when no index fits.
Online Schema Change
Index modifications—adding, disabling, or dropping an index—do not affect normal read or write operations on the primary table. You can add, delete, or update indexes at any time without downtime.
Strong consistency
Data consistency between the primary table and index tables has the following constraints:
Snapshot isolation is not supported. Data written to the primary table may not be immediately visible in index tables. After the server returns a success response, data becomes visible in both.
Client timeout or I/O error behavior. If a write fails due to a client timeout or I/O error, the data may not appear in either the primary table or index tables. Eventual consistency between them is still guaranteed.
Mutability
Every write to an indexed table costs more than a write to a plain table, because the index must be maintained. The actual cost depends on whether your workload updates or deletes existing rows.
The MUTABILITY property tells Lindorm what operations your workload performs, so it can minimize index maintenance overhead. Set this property using Table_Options when you create or modify a table. See CREATE TABLE syntax.
Choose a mutability setting based on your write pattern:
Append-only, no updates or deletes: use
IMMUTABLEAppend and row-level deletes, no updates: use
IMMUTABLE_ROWSUpdates and deletes, no custom timestamp: use
MUTABLE_LATEST(recommended)Updates and deletes with a custom timestamp: use
MUTABLE_UDT
| Workload pattern | Setting | Operation cost | Notes |
|---|---|---|---|
| No index | — | 1 | Writes directly to the primary table |
| Insert only, never update or delete | IMMUTABLE | 2 | Lowest cost. Not recommended: no safeguard prevents updates—if updates occur, the index silently becomes inconsistent with the primary table |
| Insert and delete by row, never update | IMMUTABLE_ROWS | 2–3 | Second-lowest cost. Not recommended: same caveat—updates cause silent inconsistency |
| Update and delete, no custom timestamp | MUTABLE_LATEST (recommended) | 4 | Default for LindormTable 2.7.9 and later. Best balance of safety and performance |
| Update and delete, custom timestamp required | MUTABLE_UDT | 4 | Optimized version of MUTABLE_ALL. Requires LindormTable 2.6.7 or later |
| Update and delete, custom timestamp, no version restrictions | MUTABLE_ALL | 4 | No restrictions; use MUTABLE_UDT instead for better performance |
MUTABLE_LATEST is the recommended setting. For LindormTable 2.7.9 and later, it is the default. If your instance runs an earlier version, set MUTABILITY='MUTABLE_LATEST' explicitly.
To write data with a custom timestamp, use MUTABLE_UDT (requires LindormTable 2.6.7 or later). Plan this before creating indexes—the mutability property takes effect at index creation time.
If you write data with a custom timestamp but have not set the mutability to MUTABLE_UDT or MUTABLE_ALL before creating the index, queries against the index will fail immediately after index creation. For recovery steps, see FAQ.
IMMUTABLEandIMMUTABLE_ROWShave no server-side enforcement. If your workload updates data but the table is set toIMMUTABLEorIMMUTABLE_ROWS, the server does not return an error—but index data becomes inconsistent with the primary table. Use these settings only when you are certain no updates will occur. BecauseIMMUTABLEtables involve no deletions, they fully support multi-Internet Data Center (IDC) active-active deployments.
Covering indexes
Without a covering index, a query that matches an index entry still needs to fetch the full row from the primary table. The rowkeys returned by the index may be scattered across the primary table, requiring multiple remote procedure calls (RPCs) and increasing response time (RT) as the result set grows.
A covering index includes primary table columns directly in the index table, eliminating primary table lookups for those columns. Lindorm supports three coverage modes:
Specific columns: List the columns to replicate from the primary table.
All schema columns (
COVERED_ALL_COLUMNS_IN_SCHEMA): Includes all columns currently defined in the primary table schema. When a new column is added to the primary table, the index automatically includes it—no reindexing required.Dynamic columns (
DYNAMIC): Includes all dynamic columns from the primary table, plus all schema-defined columns.
Custom timestamps (User-Defined Timestamp, UDT)
Lindorm supports column-level custom timestamps. Writing data with a custom timestamp is common in HBase-based systems for controlling Time to Live (TTL) and handling out-of-order or idempotent writes. Secondary indexes in Lindorm also support updating index data when custom timestamps are used—an uncommon capability in NoSQL systems.
Two use cases where this matters:
Concurrent import and real-time updates: Use the current time for real-time writes and a prior timestamp (for example, 23:59:59 of the previous day) for historical imports. Real-time writes take precedence; historical imports fill in only the data not yet updated.
Message catch-up: When a system skips accumulated messages and processes current ones first, each message carries its own timestamp. Catching up on earlier messages later applies the correct overwrite order, regardless of processing sequence.
Create a secondary index
After creating a Lindorm primary table, create secondary indexes on its columns using the CREATE INDEX statement.
The following example uses an orders table. An index on the user_id column lets you query orders by user without scanning the full table.
-- Create the primary table
CREATE TABLE orders (
order_id VARCHAR NOT NULL,
user_id VARCHAR,
status VARCHAR,
amount DOUBLE,
created BIGINT,
PRIMARY KEY(order_id)
) WITH (CONSISTENCY = 'strong', MUTABILITY = 'MUTABLE_LATEST');
-- Create a covering index on user_id, including all schema columns
-- Queries filtering on user_id hit this index and avoid primary table lookups
CREATE INDEX idx_user ON orders(user_id) WITH (INDEX_COVERED_TYPE = 'COVERED_ALL_COLUMNS_IN_SCHEMA');
-- This query uses idx_user automatically
SELECT * FROM orders WHERE user_id = 'u123';Choose synchronous or asynchronous index creation based on the size of your primary table. Use synchronous creation for small tables and asynchronous creation for large ones. See CREATE INDEX for syntax details.
When adding an index to a table with existing data, CREATE INDEX synchronizes historical data from the primary table to the index table. This can take a long time for large tables. The synchronization runs server-side—terminating the Lindorm Shell process does not interrupt it.If hot and cold data separation is enabled, monitor cold storage rate limiting during index building. Cold storage read throttling reduces index building speed and can cause backpressure on write operations.
View secondary indexes
Use SHOW INDEX to view the indexes on a table, including their names and current status.
SHOW INDEX FROM orders;Change index status
Use ALTER INDEX to activate or disable a secondary index.
-- Activate an index
ALTER INDEX IF EXISTS idx_user ON orders ACTIVE;
-- Disable an index
ALTER INDEX idx_user ON orders DISABLED;Do not change a DISABLED index directly to ACTIVE—this causes data loss. Run a BUILD INDEX operation first to rebuild the index data, then activate it.
If the primary table contains historical data after index creation, run a rebuild operation on the index before activating it. See BUILD INDEX.
Delete a secondary index
Use DROP INDEX to remove a secondary index from a primary table.
DROP INDEX IF EXISTS idx_user ON orders;Deleting an index requires the Trash permission. If active queries are using the index, delete it with caution.
Query optimization
Lindorm selects indexes using Rule Based Optimization (RBO). It matches the prefix of each index against the query's WHERE clause and selects the index with the highest degree of match.
The following examples show how the optimizer selects indexes for a table with multiple indexes:
CREATE TABLE dt (rowkey VARCHAR, c1 VARCHAR, c2 VARCHAR, c3 VARCHAR, c4 VARCHAR, c5 VARCHAR, PRIMARY KEY(rowkey));
CREATE INDEX idx1 ON dt(c1);
CREATE INDEX idx2 ON dt(c2, c3, c4);
CREATE INDEX idx3 ON dt(c3) INCLUDE(c1, c2, c4);
CREATE INDEX idx4 ON dt(c5 DESC) WITH (INDEX_COVERED_TYPE = 'COVERED_ALL_COLUMNS_IN_SCHEMA');| Query | Index selected | Why |
|---|---|---|
SELECT rowkey FROM dt WHERE c1 = 'a' | idx1 | Exact prefix match on c1 |
SELECT rowkey FROM dt WHERE c2 = 'b' AND c4 = 'd' | idx2 | Matches c2 prefix; c3 is missing from the WHERE clause, so c4 is filtered row by row after the index scan |
SELECT * FROM dt WHERE c2 = 'b' AND c3 >= 'c' AND c3 < 'f' | idx2 | Matches c2, c3 prefix; SELECT * requires a primary table lookup because idx2 does not include all columns—scattered rowkeys may result in multiple RPCs, increasing RT for large result sets |
SELECT * FROM dt WHERE c5 = 'c' | idx4 | idx4 is a full covering index; SELECT * does not require a primary table lookup |
A query can use at most one index at a time—index merge is not supported.
To influence index selection, use query hints. See CREATE INDEX.
Limits
Index names must be unique per primary table. The same index name can be used on different primary tables.
Indexes are supported only on single-version tables. Multi-version tables do not support indexes.
Cell-level TTL is not supported. Index tables inherit the TTL of the primary table; you cannot set a separate TTL for an index table.
For LindormTable 2.8.6 and later: up to 10 indexes per primary table, up to 8 index columns per index. For earlier versions: up to 5 indexes and up to 3 index columns.
The combined length of index columns and the primary key must not exceed 30 KB. Avoid using columns larger than 100 bytes as index columns.
A query uses at most one index. Index merge queries are not supported.
Secondary indexes do not support the batch increase feature.
The sort order of results from a secondary index query differs from that of the primary table.
Indexes cannot be built for data imported via Bulkload. Indexes apply only to data written through SQL or an API.
Creating an index on a large table causes
CREATE INDEXto run for an extended time while historical data is synchronized.
FAQ
Why does the error User-Defined-Timestamp(UDT) is not supported by table in MUTABLE_LATEST mode appear?
You created an index while the table's mutability was set to MUTABLE_LATEST, but your application writes data with a custom timestamp. MUTABLE_LATEST does not support custom timestamps.
To fix this:
Drop the index on the primary table.
Change the mutability property to
MUTABLE_UDT.Create the index again.
If active queries are hitting the index, dropping it affects those queries. Plan accordingly before proceeding.
For other index-related issues, join the Lindorm DingTalk group or submit a ticket. See Technical support.