Online multi-table joins let you run SQL queries across multiple related tables in real time, without pre-joining or denormalizing data. This guide walks you through creating an OpenSearch High-performance Search Edition instance that supports this feature, from purchasing an instance to testing your first join query.
Prerequisites
Before you begin, ensure that you have:
An Alibaba Cloud account with identity verification completed
An AccessKey (created when you first log in to the Alibaba Cloud console)
How it works
OpenSearch High-performance Search Edition uses a distributed backend. For multi-table joins to work, records from different tables that need to be joined must reside on the same node — because joins are executed locally within each column (shard) and never performed across columns.
The routing field controls this placement: when building the index, the engine hashes each record's routing field value and stores all records with the same hash in the same column. During a query, the QRS worker sends the SQL query to each column, each column executes the join locally, and the QRS worker aggregates the per-column results and returns them to the user.
This means the routing field must contain the join key. If you join on a non-primary key, configure that join key as the routing field so that records with matching values always land on the same column.
Create an instance
Go to the OpenSearch High-performance Search Edition console and click Create Instance.
Select your instance specifications. Set Edition to High-performance Search Edition.
Setting Description Billing method Subscription or Pay-as-you-go Region and zone Select the region and zone closest to your users Application name Enter a custom name Cluster preference Storage-optimized Dedicated Cluster (required for multi-table joins) Storage capacity Accept the default or specify a custom value Computing resources Accept the default or specify a custom value 
Confirm your order, accept the terms of service, and click Activate Now.
Wait for the instance to appear in the instance list with a status of Pending.
Configure the application
Define the application schema
On the instance list page, find your instance and click Configure in the Actions column.
Choose a method to define your application schema: For field type reference, see Application schema in OpenSearch High-performance Search Edition.
The total number of tables cannot exceed eight.
Method When to use From a template Reuse a previously saved schema By uploading a document Upload a JSON data file; the system generates an initial schema that you then refine From a data source Sync from RDS or MaxCompute; the system generates an initial schema from the source table schema, reducing manual setup. See Data source configuration Manually Define fields from scratch; used in this guide to create two tables 
Configure the index schema
Configure the index schema separately for each table.
Keep the following field type restrictions in mind:
FLOAT,FLOAT_ARRAY,DOUBLE, andDOUBLE_ARRAYfields cannot be set as index fields.TEXTandSHORT_TEXTfields cannot be set as attribute fields.
Set a field as an attribute field to use it in SELECT, WHERE, or ORDER BY clauses.
For guidance on configuring the index schema and choosing analysis methods, see Index schema and Text analyzers.
Configure the routing field
Connect a data source
Click Add Data Source and select the data source type. Supported data source types:
After completing the data source configuration, click Completed.
On the instance details page, wait until the offline application status changes to Normal. The application is ready for queries once it reaches this state.
Test with a SQL join query
After the application status reaches Normal and the application is published, go to the Search Test page to run test queries.
Limitations
The following limitations apply when using online multi-table joins:
| Limitation | Reason |
|---|---|
| Only Exclusive Storage-optimized clusters support multi-table joins | The join-per-column architecture requires dedicated storage nodes with co-located data |
| Only SQL queries are supported | The distributed join execution is built on the SQL engine |
| Custom analyzers are not supported | Custom analyzers are incompatible with the multi-table index structure |
| Sort configurations are not supported | Pre-configured sort policies conflict with per-column join aggregation; use ORDER BY in your SQL query instead |
| Search result display settings are not supported | Result formatting is controlled by the SQL query output |
| Exclusive Storage-optimized cluster instances cannot be upgraded or downgraded | The cluster type is fixed at creation to maintain storage-optimized guarantees |
What's next
SQL support — Full reference for the SQL syntax supported in join queries
Data source configuration — Configure additional or updated data sources
Application schema in OpenSearch High-performance Search Edition — Detailed field type and schema reference