Search code examples
sphinxord

Sphinx seems to force a Order on ID?

I added a new field to my index (weight) which is an integer based value I want to sort on.

I added it to the select and invoked it as sql_attr_uint

When I call it in my query it shows up. However when I try to sort on it i get strange behavior. It always sorts on the record ID instead. So Order on ID is identical to Order on Weight.

I've checked the Index pretty thoroughly and can't find a reason why, does sphinx auto-sort on record ID somehow?

I know the details are fairly sparse yet I'm hoping there is some basic explanation I'm missing before asking anyone to delve in further.

As an update: I don't believe the ID field sort has been "imposed" on the index in anyway inadvertently since I can Order by a # of other fields, both integer and text and the results are returned independent of the ID values (e.g sort on last name Record #100 Adams will come before Record #1 Wyatt)

Yet ordering on Weight always returns the same order as ordering by ID whether it is asc or desc. No error about the field or index not existing or being sortable, no ignoring the order request (desc and asc work) it just ignores that particular field value and uses the ID instead.

Further Update: The Weight value is indexed via a join to the main table indexed by sphinx in the following manner:

sql_attr_multi = uint value_Weight from ranged-query; \
SELECT j.id AS ID, IF(s.Weight > 0, 1, 0) AS Weight \
FROM Customers j \
INNER JOIN CustomerSources s ON j.customer_id = s.customer_id \
AND j.id BETWEEN $start AND $end \
ORDER BY s.id; \
SELECT MIN(id), MAX(id) FROM Customers

Once indexed sorting on both id and value_Weight return the same sort whereas Weight and ID are unrelated.

Solution

  • Ah yes, from http://sphinxsearch.com/docs/current/mva.html

    Filtering and group-by (but not sorting) on MVA attributes is supported.

    Can't sort by a MVA attribute (which as noted in comments makes sense, as MVAs usually contain many values, sorting by many values is rather 'tricky'.

    When you try, it simply fails. So sorting is falling back on the 'natural' order of the index, which is usually by ID.

    Use sql_attr_unit instead http://sphinxsearch.com/docs/current/conf-sql-attr-uint.html (but will proabbly mean rewriting the sql_query to perform the JOIN on CustomerSources )