0

I have items and list in which categories exists:

id | name       | categories(varchar)
1  | bike red   | 2,5,18
2  | bike black | 4,7,13

With Sphinx I need to serach for example: bike AND only from category 5

Is any good way how search in column categories?

In MySql I could write: WHERE name LIKE '%bike%' AND categories LIKE '%5%'

But my Sphinx index is big and searching could be not efective. Is any way like create integer ENUM list or? What could be good solution?

Thanks

Poul D.
  • 59
  • 7

1 Answers1

0

Sphinx has Multi-Value Attributes http://sphinxsearch.com/docs/current.html#mva . pretty much perfect for this!

It works kinda like a numeric set in MySQL! (you have multiple categories, so set, not enum)

It will even automatically parse a string list of numbers seperated by commas during indexing. 

sql_query = SELECT id,name,categories FROM item
sql_attr_multi = uint categories from field;

Then a sphinxQL query...

SELECT * FROM item WHERE MATCH('bike') AND categories=5

(This may look confusing if familar with MySQL. an equality filter on a MVA attribute, actully just means equals one of the values. If want could write categories IN (5) - same effect)

barryhunter
  • 20,886
  • 3
  • 30
  • 43