Prepare JOIN index

OpenSearch
1

Versions (relevant - OpenSearch/Dashboard/Server OS/Browser):

AWS OpenSearch 2.11

Describe the issue:

I have a simple problem, but the solution seems to be more complex then I first anticipated. I would like to have 2 indexes combined in the result. This is working with the SQL query, so that is not the issue. But with big datasets it can take 20 seconds or longer for a result. I understand that I need to avoid JOINS with OpenSearch, but I need to find another way to resolve this.

I have a MySQL database, with 2 tables.

  • 1 table with users (id;name;email)
  • 1 table with phonenumbers (id;user_id;phonenumer)

This is synced to OpenSearch. So these 2 tables are also in OpenSearch.

With the SQL query I can receive the data from OpenSearch

POST _plugins/_sql
{
   "query": "SELECT * FROM phonenumbers JOIN users ON users.id = phonenumbers.user_id WHERE phonenumbers.phonenumer LIKE  '%5412%'"
}

This works fine, but for large datasets it take a while.
My question is, how can I combine the data from these 2 indexes to 1 to avoid the JOIN in the search query?

So I get something like this:

{"users": {
  "id": "1",
  "name": "don",
    "phonenumers": [
      {"id": "1", "phonenumber": "0000000000"},
      {"id": "2", "phonenumber": "0000000001"},
      {"id": "2", "phonenumber": "0000000002"}
    ]
}}

This should speed up the search I think. Or are there other options with alias/ aggregate?