Thinking Sphinx - sorting by a string attribute gets out of sync when changes are made

Question

I have a "restaurants" table with a "name" column. I've defined the following index:

indexes "REPLACE(UPPER(restaurants.name), 'THE ', '')", :as => :restaurant_name, :sortable => true

... because I want to sort the restaurant names without respect to the prefix "The ".

My problem is that whenever one of these records is updated (in any way) the new record jumps to the top of the sort order. If another record is updated, it also jumps ahead of the rest. I end up with two lists: a list of restaurants that have been updated since the last re-indexing and a list of those that haven't. Each respective list is in alphabetical order, but I don't understand why the overall list is getting segregated this way. I do have a delayed delta index set up, and I assume the issue is related to this.

Answer 1

Unfortunately this is a limitation of sphinx.

To sort by strings it builds an ordered list of values, then converts them to an integer representing the place in the list. This happens on a per index basis, so when the first item gets added to the delta index, you end up with 2 records that sort with a 1. The next record means you'll have 2 records that sort with a 2, etc.

Answer 2

I handled this by creating my own sort method. You can create an integer value based on some part of the string. I took a cheap approach and just used the char value of the first character. You could get more complex by creating an algorithm that converts more characters in the phrase. When I did search using another search engine we used 6 characters and rarely did that get out of order and that was our entire order method. If you do this and make it a secondary order by then it will help resolve your deltas issues.