How to search in Sphinx based on permissions?

Question

Sphinx

Hello!

Recently started using Sphinx. Faced a problem, the solution which I did not find in the documentation.

So, the task: it is necessary to perform a search in a table with a check of access rights that are stored in another table.

How I do it now:
1. I index a table with data (1.5 million records)
2. I get a list of IDs from Sphinx when searching
3. I inner join this list with the IDs of all records available to the user (from the security table)

In the current solution does not suit:
1. loads the DBMS
2. it is necessary that the search server be as independent as possible, so that when searching, the data server is loaded to a minimum.

Possible Solution:
We create a second index of access rights and, when searching, combine the search results from two indexes (passing an additional user id).

I will be grateful for your help!

Answer 1

It all depends on how these rights are organized. For example, I have group rights. The index contains the attribute sql_attr_multi = bigint usergroups from field. -1 all users, then groups: 0 anregs, 1, 2… Well, then I put a filter when searching [-1, curent_user_group].

Answer 2

Any query can be placed in sphinx indexes. I understand that you are doing inner join outside of sphinx. So make such a request, which from sphinx will already return only those id to which the user has rights and that's it.

Answer 3

Of course, I don’t know the structure of your database, but if it is possible to do without join, it’s better to do it.
And so the user karenishe is right. Make a request in the source'a body itself by searching immediately with the request and into the rights table.
A la: sql_query= SELECT * FROM first_table INNER JOIN users ON…