Searching over multiple Indexes

OpenSearch
1

Hey,

I’m new to opensearch and don’t know if this is the right category but let’s see:

I want to perform searches to a relative complex set of data,i think its a really common usecase, but i’m not sure how to setup my indexes and perform the search.
In my database i have a table with items and all informations.
in a second table i have all that items mapped to users that own items in their library. Items can be owned multiple times and have some additional information for each user.
In a third table i have the price history for the items.
Now i want to perform a search for a user over his items, but he needs to able to use all information thats stored in the item table to perform the search and get the current price of his library.
I also need to group that data and get the total prices for a libray of a user.

I’m not sure whats the best way to index and search that data. If i join all data in one index it will get really huge and if there is a change to an item i need to update all users items.

Here are some sizes for the different tables:
Items: 600000
Items for each user: 1000-200000
Prices: 1 for each Item per Day

Thanks for your answers in advance
Jupdi

2

Hi @Jupdi ! Thanks for bringing your question here. I’m curious - what kind of database are you coming from? It sounds like you’re dealing with an SQL style database that has been normalized a bit. That is to say, the data is stored in different tables but referred to by joining with tables that have referential information. I’d hate to assume - can you offer more information?

OpenSearch has no such requirement nor any real concept of tables. It will index every entity in the json you ingest. I would submit that putting all of the data you need into one index is the appropriate use of your data if your goal is to aggregate and visualize on every piece of it.

A Lucene Index on average uses up about 20 to 30 percent of the size of the ingested document’s text. If you don’t need to store the json itself that you index either, I suspect you won’t find the number of potential entries quite so daunting.

Check out this section of our documentation to see some of the ways you can fine tune your index: Index templates - OpenSearch documentation

Let me know if I’ve been helpful!

Nate