elastic search fulltext search on multiple index

0
votes

Design Query for elasticsearch:

I have 10 tables in my mysql database : news, emails, etc. Which i would sync into elasticsearch. and i want to search across all these tables in the same go. There are no relationship in tables and all have txt field in them. Just want to search in txt field .. so should i have multiple index or just 1 index.

How should i organize my indices:

Option 1 : Should i have just one elasticsearch index(with an attribute of table type) for all the tables

OR

Option 2 : Should i have just multiple elasticsearch index for all the tables

Considering:

  • want to make combined query in multiple data source ordered by hits . Example : search all email + news ..
  • or single query to only search email or news only
2
elasticsearch
Needs more context. Generally speaking, you'll need to flatten your tables... The flattening depends on what the table relationships are? What's a concrete query you want to implement? – Joe - Elasticsearch Handbook
there are no relationship in tables and all have txt field in them .. just want to seach in txt field .. so should i have multiple index or just 1 index .. – shrw

2 Answers

2
votes

Have multiple indices and query any number of them at any given time:

POST emails/_doc
{
  "txt": "abc"
}

POST news/_doc
{
  "txt": "ab"
}

GET emails,news/_search
{
  "query": {
    "query_string": {
      "default_field": "txt", 
      "query": "ab OR abc"
    }
  }
}

Wildcard index names are supported too in case you've got, say, timestamp-bucketed names such as emails_2020, emails_2019 etc:

GET em*,ne*/_search
...