Lindorm SQL FAQ

Q: How do I resolve or avoid inefficient queries?

A: If your query returns the error This query may be a full table scan and thus may have unpredictable performance, the query is considered an inefficient query.

What is an inefficient query? On LindormTable, a query with filter conditions that cannot effectively use the primary key or an existing index forces a full table scan. Such queries are considered inefficient. LindormTable blocks them by default to protect database performance and stability.

The matching rules follow the leftmost prefix rule — the same rule MySQL uses for composite indexes. The system matches the columns in your WHERE clause against the primary key (or index key) columns starting from the leftmost column. If your query skips the first column, the key is not used and a full table scan results.

For example, if the table test has a composite primary key (p1, p2, p3) and you run:

SELECT * FROM test WHERE p2 < 30;

The query skips p1, so LindormTable cannot use the primary key. The entire table is scanned to satisfy the p2 < 30 condition.

To fix or avoid this:

• Include the primary key's first column in the WHERE clause, following the leftmost prefix rule.

• Redesign the table's primary key. See How to design a primary key for a wide table.

• Create a secondary index on the columns you query. See Secondary indexes.

• For multidimensional queries across multiple columns, create a search index. See Search indexes.

• To force execution of the inefficient query, add the /*+ _l_allow_filtering_ */ hint:

  SELECT /*+ _l_allow_filtering_ */ * FROM dt WHERE nonPK = 100;

Q: Why does a GROUP BY query fail with a "subPlan groupby keys" error?

A: Error message:

The diff group keys of subPlan is over lindorm.aggregate.subplan.groupby.keys.limit=..., it may cost a lot memory so we shutdown this SubPlan

The GROUP BY operation produced too many groups. Large numbers of groups consume excessive memory and increase instance load, so LindormTable shuts down the subplan.

To resolve this:

• Add filter conditions to reduce the number of groups before aggregation.

• For multidimensional aggregation scenarios, create a search index to offload the work. See Search indexes.

• To increase the group count threshold, contact Lindorm technical support (DingTalk ID: s0s3eg3).

Q: Why does SELECT * on a dynamic columns table fail with a "Limit not set" error?

A: Error message:

Limit of this select statement is not set or exceeds config when select all columns from table with property DYNAMIC_COLUMNS=true

Tables with dynamic columns have no fixed schema and can contain a large and unpredictable number of columns. Running SELECT * without a row limit causes high I/O and increases instance load, so LindormTable requires a LIMIT clause on such queries.

Add a LIMIT clause to the SELECT statement:

SELECT * FROM test LIMIT 10;

Q: Why does a query fail with "Code grows beyond 64 KB"?

A: The Lindorm SQL engine uses Just-In-Time (JIT) compilation: it generates bytecode from the query's physical plan and compiles it at runtime. This error means the bytecode for a generated method exceeds the 64 KB limit imposed by the Java Virtual Machine (JVM).

The most common cause is a predicate in the SQL query statement that is too long or too complex, resulting in bytecode that is too large to be executed.

Simplify the predicate expressions in the SQL statement. Break complex conditions into smaller parts or rewrite the logic to reduce bytecode size.

Q: Why does a query fail with "The estimated memory used by the query exceeds the maximum limit"?

A: The SQL engine consumes significant memory when processing result sets — during aggregation, sorting, or deduplication. Because Lindorm SQL is designed for high-concurrency online workloads, it limits each query to 8 MB of memory by default. Exceeding this limit triggers a memory overflow exception.

Step 1 — Diagnose before acting:

Check the execution plan to determine whether aggregation and sorting operators are pushed down to the storage engine or executed in the SQL engine. See Interpret an execution plan.

• If heavy operators run in the SQL engine, query optimization is the right fix (see Option 1).

• If operators are already pushed down and the query is already optimized, raise the memory limit (see Option 2).

Option 1 — Optimize the query (preferred):

Push aggregation and sorting down to the storage engine using indexes, and tighten filter conditions to reduce the amount of data the SQL engine processes.

Option 2 — Raise the memory limit:

If the query is already optimized and you need a higher limit, adjust QUERY_MAX_MEM using ALTER SYSTEM:

ALTER SYSTEM SET QUERY_MAX_MEM = 8388608;

Check the current value with the SHOW VARIABLES statement.

If your SQL engine version is earlier than 2.9.6.0, contact Lindorm technical support (DingTalk ID: s0s3eg3) to increase the limit.

Q: Why is it discouraged to use many IS NULL conditions in a single WHERE clause?

A: In LindormTable, IS NULL must handle both "the column exists with a NULL value" and "the column does not exist or was never written". When a SQL statement contains multiple IS NULL predicates, the SQL engine may expand these conditions into combinations during compilation. N IS NULL predicates can theoretically produce up to 2^N branches, significantly increasing compilation time and memory usage. In severe cases, this can affect query execution and instance stability.

Recommendation: Avoid combining many column IS NULL conditions in a single SELECT, UPDATE, or DELETE statement. Consider the following approaches:

• Narrow the query range using primary keys, secondary indexes, or search indexes whenever possible.

• If your business logic frequently checks whether fields are empty or present, express that business state explicitly on the write side — for example, with default values or status columns.

• For batch processing, split the work into multiple SQL statements with simpler conditions, or fetch the full primary keys first and process them in batches by primary key.

Q: Why is it discouraged to use many AND-combined OR groups in a WHERE clause?

Problem: A SQL statement whose WHERE clause connects multiple parenthesized OR groups with AND, for example:

SELECT * FROM orders
WHERE (status = 1 OR status = 2)
  AND (pay_type = 'wechat' OR pay_type = 'alipay')
  AND (region = 'CN' OR region = 'US')
  AND ...;

The deeper the nesting, the greater the risk. This applies to SELECT, UPDATE, and DELETE statements alike.

A: Before executing a query, the optimizer must convert the WHERE clause into Disjunctive Normal Form (DNF) — an OR-set of AND-conditions — so that it can select the optimal index access path for each independent condition. When the optimizer encounters (A OR B) AND (C OR D), it must apply the distributive law to expand it:

(A∨B)∧(C∨D) ⇒ (A∧C)∨(A∧D)∨(B∧C)∨(B∧D)

(A or B) and (C or D) ⇒ (A and C) or (A and D) or (B and C) or (B and D)

• 2 groups, each with 2 branches: 2×2 = 4 combination terms.

• 3 groups, each with 2 branches: 2×2×2 = 8 combination terms.

As the number of groups and branches per group grows, the number of combination terms increases exponentially. This can significantly increase optimizer compilation time and memory usage, and in severe cases may affect query execution and instance stability.

Recommendation:

• Avoid combining many parenthesized OR groups with AND in a single SQL statement.

• Rewrite same-column OR conditions with IN — for example, status IN (1, 2) — to reduce the number of expanded branches.

• Narrow the query range using primary keys, secondary indexes, or search indexes whenever possible.

• For multidimensional matching scenarios, use a search index. See Search indexes.

• If complex conditions are required by the business logic, split the work into multiple simpler SQL statements, or fetch the full primary keys first and process them in batches by primary key.

Q: How do I diagnose and resolve a memory limit error for a specific query?

A: When a query fails with the memory limit error, follow these steps to diagnose the root cause and choose the appropriate solution:

Step 1 — Check the execution plan:

Run EXPLAIN on the failing query to view its execution plan. See Interpret an execution plan for details.

In the execution plan, identify whether memory-intensive operators — such as aggregation, sorting, and deduplication — run in the SQL engine or are pushed down to the storage engine. Operators that run in the SQL engine consume memory from the per-query limit, while pushed-down operators use storage engine resources instead.

Step 2 — Try to optimize the query:

If memory-intensive operators run in the SQL engine, try the following approaches to reduce memory consumption:

• For aggregation operators (GROUP BY, COUNT, SUM, AVG): Create a secondary index or search index on the grouped or aggregated columns to enable operator pushdown to the storage engine.

• For sorting operators (ORDER BY): Ensure that the sort columns align with an existing index to avoid in-memory sorting. Alternatively, add tighter WHERE conditions to reduce the dataset size before sorting.

• For deduplication operators (DISTINCT): Use a search index for queries on high-cardinality columns, or add selective filter conditions to reduce the number of rows processed.

Step 3 — Raise the memory limit if needed:

If all operators are already pushed down and the query cannot be further optimized, raise the QUERY_MAX_MEM value using ALTER SYSTEM:

ALTER SYSTEM SET QUERY_MAX_MEM = <new_value_in_bytes>;

Check the current value with the SHOW VARIABLES statement.

Q: Why does creating a secondary index fail with "Executing job number exceed, max job number = 8"?

A: Each Lindorm instance allows a maximum of 8 concurrent secondary index build tasks. If 8 tasks are already running, new index creation attempts fail.

Avoid creating many secondary indexes at the same time. If you need to create a large number of indexes in bulk, contact Lindorm technical support (DingTalk ID: s0s3eg3).

Q: After deleting a column, why does re-adding a column with the same name fail with "column is under deleting"?

A: After you delete a column, LindormTable asynchronously cleans up the column's data from memory, hot storage, and cold storage. The system prevents re-adding a column with the same name until cleanup completes — to avoid dirty data caused by type mismatches or data collisions.

The cleanup runs in the background and can take a long time. To accelerate it, run the following statements on the table (replace dt with your table name):

-- Flush residual in-memory data to storage.
ALTER TABLE dt FLUSH;

-- Run compaction to merge and remove deleted data.
ALTER TABLE dt COMPACT;

After the cleanup completes, re-add the column.

Q: After creating a secondary index, why does writing data fail with a "User-Defined-Timestamp" error?

A: Error message:

Performing put operations with User-Defined-Timestamp in indexed column on MULTABLE_LATEST table is unsupported

When you write data with an explicit custom timestamp — for example, using the /*+ _l_ts */ hint in an UPSERT statement — both the primary table and the secondary index table must have their mutability set to MUTABLE_ALL. However, Lindorm defaults new tables and indexes to MUTABLE_LATEST for performance reasons. Writing with a custom timestamp to an index table with MUTABLE_LATEST mutability triggers this error.

The MUTABILITY property cannot be changed after an index table is created. You must delete the existing index, update the primary table's mutability, and then recreate the index.

1. Disable and drop the existing secondary index:

   -- Disable the index.
   ALTER INDEX IF EXISTS <original_secondary_index_name> ON <primary_table_name> DISABLED;
   
   -- Drop the index.
   DROP INDEX IF EXISTS <original_secondary_index_name> ON <primary_table_name>;

   See Delete a secondary index.

2. Set the primary table's mutability to MUTABLE_ALL:

   ALTER TABLE IF EXISTS <primary_table_name> SET MUTABILITY='MUTABLE_ALL';

3. Create a new secondary index. See CREATE INDEX.

Q: Why are batch updates not supported, or why does the error "Update's WHERE clause can only contain PK columns" occur?

A: Only single-row updates are supported by default. For information on how to enable batch updates, see Batch operations FAQ.

Q: How do I enable batch deletes?

A: For information on how to enable and configure batch deletes, see Batch operations FAQ.