Search code examples
sphinx

Are there plans to add 'OR' to attribute searches in Sphinx?

A little background is in order for this question since it is on surface too generic:

Recently I ran into an issue where I had to move the attribute values I was pushing into my sphinxql query as full-text because the attribute needed to be part of an 'OR' query.

In other words I was doing:

Select * from idx_test where MATCH('Terms') and name_id in (1,2,3)

When I tried to add an 'OR' to the attributes such as:

Select * from idx_test where MATCH('Terms') and name_id in (1,2,3) OR customer_id in (4,5,6)

it failed because Sphinx 2.* does not support OR in the attribute query.

I was also unable to simply put the name and customer IDs in to the query:

Select * from idx_test where MATCH('Terms  ((@(name_id) 1|2|3)|(@customer_id) 4|5|6))')

Because (as far as I can tell) you can't push integer fields into the full_text search.

My solution was to index the id fields a second time appended by _text:

Select name_id, name_id as name_id_text

and then add that to the field list:

sql_attr_uint = name_id
sql_field_string  = name_id_text
sql_attr_uint = customer_id
sql_field_string  = customer_id_text

So now I can do my OR query as full_text:

Select * from idx_test where MATCH('Terms  ((@(name_id_text) 1|2|3)|(@customer_id_text) 4|5|6))')

However recently I found an article that discussed the tradeoff between attribute and full-text searches. The upshot is that "it could reduce performance of queries that otherwise match few records". Which is precisely what my name_id/city_id query does. In an ideal world then I'd be able to go back to:

Select * from idx_test where MATCH('Terms') and name_id in (1,2,3) OR customer_id in (4,5,6)

If Sphinx would only allow for OR between attributes since as far as I can tell once I have a query that is filtering down to a relatively low # of results I'd have a much faster query using attributes vs full_text.

So my two-part question therefor is:

  1. Am I in fact correct that this is the case (a query that would reduce the # of results significantly is better served doing attributes then full-text)?
  2. If so are there plans to add OR to the attribute part of the SphinxQL query?
  3. If so, when?
Solution

  • While yes, OR is not supported directly in WHERE, can still run the query. Your 

    Select * from idx_test where MATCH('Terms') and name_id in (1,2,3) OR customer_id in (4,5,6)

    example can be written as 

    Select *, IN(name_id,1,2,3) + IN(customer_id,4,5,6) as filter 
   from idx_test where MATCH('Terms') and filter > 0

    It is a bit more cumbersome, but should work. You still get the full benefit of the full-text inverted index, so performance actully shoudnt be bad. The fitler is only executed against docs matching the terms. (this may look crazy, if coming from say mysql background, but remeber sphinxQL isnt mysql :)

    You dont get 'short circuiting (ie customer_id filter, will still be run, even if matches name_id), so perhaps 

    Select *, IF(IN(name_id,1,2,3) OR IN(customer_id,4,5,6),1,0) as filter 
   from idx_test where MATCH('Terms') and filter =1

    is even better, the if function has an OR operator! (as sphinx could potentially short-circuit, but don't know if it does)

    (but also yes, if the 'filter' is highly selective (matching few rows), than including in the full-text query can be good. As it discards the rows earlier in processing. The problem with non-selective filters, is they have lots of matching rows, so a long doclist to process during text-query processing)