Sphinx centralize multiple tables into a single index

450 views Asked by At

I do have multiple tables (MySQL) and I want to have a single index for them.

Each table has the primary key of int autoincrement type.

The structure of collected data is the same for each table (so no conflict), but as the IDs collide so it seems that I have to query each index separately (unless you can give me a hint of how to avoid ID collision)

Question is: If I query each index separately does it means that the weight of returned results are comparable between indexes?

1

There are 1 answers

1
barryhunter On BEST ANSWER

unless you can give me a hint of how to avoid ID collision

See for example http://sphinxsearch.com/forum/view.html?id=13078 You can just arrange for the ids to be offset differently. The 'sphinx document id' doesnt have to match the real primary key, but having a simple mapping makes the application simpler.

You have a choice between one-index, one-source (using a single sql query to union all the tables together. one-index, many-source. (a source per table, all making one index) or many-indexes (one index per table, each with own source). Which ever way will give the same query results.

If I query each index separately does it means that the weight of returned results are comparable between indexes?

Pretty much. The difference should be negiblibe that doesnt matter whic way round you do it.