Search over multiple indexes

Question

I've got three tables in my DB

document
| id | org_id | content |

Relation between organization and document is 1:1. Organization may have only one document.

Relation between organization and person is 1:n. Organization may have many persons.

Table document has about 12 000 000 records and takes more than 500 Gb on disk. Records in the table never change.

Table organization has about 30 000 000 records but takes only 8 Gb on disk. Records in the table are changing quite often, so I need do full re-indexing.

Here are my indices:

source organization {
        sql_query = select * from organization
        sql_attr_string attribute_1
        sql_attr_string attribute_2
}

source document {
        sql_query = select * from document
        sql_attr_uint org_id
}

source person {
        sql_query = select * from person
        sql_attr_uint org_id
        sql_attr_uint status
}

The typical request looks like this:

Find the first 100 organization containing "Acme" in name, "bankrupt" in document and has person with name "John".

How can I do this?

P.S. I use Sphinx API, but I can switch to SphinxQL

score 0 · Accepted Answer · answered Mar 20 '17 at 11:20

0

Well Sphinx itself can't do JOINs between indexes. Its only multi-index capability, is to do UNION style queries.

... so to do this need to build a 'prejoined' index. A single index combineing data from all three indexes. So can then search in any combination.

sql_query is just an arbitrary database query, and most database engines support joins, so can do the JOIN there. As the query is probably going to be expensive, can break it down with Ranged Queries. http://sphinxsearch.com/docs/current.html#ex-ranged-queries

Sphinx does have GROUP BY, so can group by either organization or document, if just want the one row for either. (as the index may contain multiple rows, becuase of multiple people

answered Mar 20 '17 at 11:20

barryhunter

20,886
3
30
43

thanks for the answer. But prejoined index, unfortunately, takes me back to a huge index, which will be re-indexed for ages – Andrew Kotov Mar 20 '17 at 16:04
**Shard it** so can do it bits :) - ie only need to rebuild bits of the index at a time :) – barryhunter Mar 20 '17 at 19:46
Sounds good! But I have no idea how to do this :( Could you give me an example of sharding? – Andrew Kotov Mar 20 '17 at 21:54
please, help me with sharding – Andrew Kotov Mar 22 '17 at 20:28
http://gatherer.wizards.com/Pages/Card/Details.aspx?multiverseid=174881 – barryhunter Mar 23 '17 at 17:31
Sorry wrong link :) Try this one: https://www.percona.com/blog/2013/01/16/sphinx-search-performance-optimization-multi-threaded-search/ – barryhunter Mar 23 '17 at 17:32
Thank you! Really useful article – Andrew Kotov Mar 25 '17 at 07:00

Search over multiple indexes

1 Answers1