Recommended method to search multiple indexes

General Discussions - English
1

Hello,

I’m a new user of Manticore Search and I must say that I’m very impressed. I was using ElasticSearch before. Thank you for creating such great software.

My question is: what’s the best way to search multiple indexes?
I have three indexes:

  1. Person
  2. Address
  3. Phone

The result that I’m looking for is similar to what a query with one or more JOINs would output in T-SQL when searching on fields in multiple tables but within the same query.
For example:

SELECT
   * 
FROM
   person 
   LEFT JOIN
      phone 
      ON phone.person_id = person.id 
WHERE
   person.full_name = 'John Doe' 
   OR phones.phone_number = '0112223'

Do I have to run multi-queries here? My main point of communication with Manticore is through its HTTP API.

2

You can create a distributed index referencing your 3 indices, a query on the distributed index will then query the 3 indices and coalesce the results. Your SQL query can also specify the join conditions. I think this should do more or less what you need.

3

Thank you for your kind words.

Unfortunately Manticore can’t do JOINs yet, so you would have to denormalize your data, so person, address and phone are in the same index.

4

Thank you for your reply. I created a distributed index with multiple local indexes but I was getting inaccurate results when searching. I don’t see documentation about the expected behavior for this case so I’m not sure what’s happening even after much testing.

5

I denormalized the datasets and I got the result that I was looking for. I considered denormalizing before creating this thread but I thought of it as the last option if nothing else works. Thank you for your suggestion. Are JOINs in the roadmap?

6

Not in the nearest plans, but eventually we would like to add it too. Perhaps in 2022.

1 Like
7

Any update on the “join”?

8

yes it is on the pay INNER/LEFT JOIN · Issue #1673 · manticoresoftware/manticoresearch · GitHub

You could subscribe at the ticket to get informed for progress

1 Like