Accelerate Table Store Queries with Multi‑Index SQL Push‑Down Operators

Multi‑Index SQL Operator Support

Multi‑index is a key feature of Table Store that uses inverted‑index technology to provide fast retrieval and statistical aggregation on non‑primary‑key columns. By default, the SQL engine reads data from the raw table, requiring a full‑table scan for non‑primary‑key queries. Enabling multi‑index allows the engine to push down eligible filter conditions and aggregation operators to the index, converting part of the query plan into an index request and improving query efficiency.

1 Prerequisites

Before using SQL to query a multi‑index, you must create the index on the table. The index must include all columns referenced in the SQL statement for push‑down to occur. For example, if table exampletable has columns a, b, c, d and the index covers b, c, d, only queries that involve b, c, d can be satisfied from the index.

SELECT a, b, c, d FROM exampletable; /* Index does not contain a,b,c,d – full‑table scan, no push‑down */

SELECT b, c, d FROM exampletable; /* Index contains b,c,d – data read from index, push‑down supported */

2 Supported Push‑Down Operators

Table Store supports push‑down for certain logical and relational operators in WHERE clauses, as well as for aggregation functions and LIMIT clauses.

Logical operators : AND, OR are supported; NOT is not supported for push‑down.

Relational operators : =, !=, <, <=, >, >=, BETWEEN … AND … are supported only when comparing a data column with a constant. Comparisons between two data columns cannot be pushed down.

Aggregation functions :

Basic aggregates: MIN, MAX, COUNT, AVG, SUM, ANY_VALUE

Distinct aggregates: COUNT(DISTINCT col_name)

Group‑by aggregates: GROUP BY col_name

Push‑down is possible only when the aggregate function and any GROUP BY arguments are data columns.

LIMIT clause : LIMIT row_count and ORDER BY col_name LIMIT row_count can be pushed down only when the ORDER BY column is a data column.

2 Multi‑Index SQL Query Practice

To demonstrate the capabilities, we use an example bike‑rental order table named trips with 1.2 million rows. The table includes columns such as order ID (primary key), rental duration, start/end dates, start/end stations, etc. Without a multi‑index, counting rows exceeds the scan quota and the query aborts.

Similarly, queries that filter on non‑primary‑key columns also fail due to full‑table scans.

Creating a multi‑index that covers all data columns enables fast queries and aggregations; however, synchronizing the index for large tables may take considerable time.

1 Filter Condition Push‑Down

After enabling the multi‑index, non‑primary‑key column filters become fast. For example, querying orders with start station IDs 31208 to 31209 returns 140 rows in about 500 ms.

The index also supports combined conditions, such as retrieving orders on 2020‑10‑01 at station 31208.

2 Statistical Aggregation Push‑Down

Beyond filters, the multi‑index provides powerful aggregation. With the index, total row count, distinct station count, and average rental duration can be obtained in roughly 500 ms.

Group‑by aggregations are also fast, e.g., listing total orders per station.

Filter conditions and aggregations can be combined, such as counting orders per station on 2020‑10‑01.

3 TopN Push‑Down

SQL statements with ORDER BY and LIMIT form TopN queries. The multi‑index can quickly retrieve the latest 10 rows.

JDBC Database Development Introduction

JDBC (Java Database Connectivity) is a Java API for executing SQL statements across various relational databases. It provides a standard set of classes and interfaces that enable developers to build database applications.