Jaak Kütt
Jaak Kütt

Reputation: 2656

How to avoid column duplication in Sphinx indexes?

I have a main source in my sphinx.conf file which in the ballpark looks like this:

sql_query = \
    SELECT \
        a.id, \
        a.filter_by_id, \
        a.filter_by_bool, \
        a.search_from_1, \
        b.search_from_2, \
        c.search_from_3, \
        a.sort_by_date, \
        d.sort_by_float \
    FROM \
        table1 AS a \
    LEFT JOIN \
        table2 AS b ON a.b_id = b.id \
    LEFT JOIN \
        table3 AS c ON a.c_id = c.id \
    LEFT JOIN \
        table4 AS d ON a.id = d.a_id AND d.domain = 1 

sql_attr_uint       = filter_by_id
sql_attr_bool       = filter_by_bool
sql_field_string    = search_from_1
sql_field_string    = search_from_2
sql_field_string    = search_from_3
sql_attr_timestamp  = sort_by_date
sql_attr_float      = sort_by_float

and then I have 9 inherited sources which only overwrite the sql_query = ... d.domain = N part... aaand after that I define 10 separate indexes for each soure.... and thus make my searchd.exe use up 10 times the memory.

Just because for every record in table1 I have a different value for sort_by_float per each domain.

Is there a way on the Sphinx side how to deal with such overlaps on indexes or I should just resort to binding all those different domain sort_by_float values as separate columns on one index?

Initial tables are all fairly small between 1-10m records, Sphinx indexes are plain and pretty much fall on default settings.

Upvotes: 0

Views: 79

Answers (1)

barryhunter
barryhunter

Reputation: 21091

should just binding all those different domain sort_by_float values as separate columns on one index?

Yes. That sounds the best idea. Sort of a pivot table.

Also sql_field_string makes a column BOTH an string attribute and a field. If you dont need it as a attribute, can save memory by leaving it just as a field.

Upvotes: 1

Related Questions