Tips and discussion: using cutoff to increase performance

We noticed for cjk queries performance is real slow and takes several seconds for certain common letters.
We usually order by date descending, with the id values in origin MySQL database going from a low value to high value.
In our testing cutoff can have a huge performance benefit but have to be very careful that the results are the same compared to search without cutoff

On testing Sphinx/Manticore search we noticed that internally it seems the searchd searches from low id to high id.
So when using a cutoff it will cutoff from low id to high id.
Therefore to use a cutoff you must put the id in the same order you want the results.
So if you have the id values in reverse of the values you wish to order you will not be able to use cutoff.
In the above case the id values are in reverse of the order we wish to sort(by date).

However, a simple method is to reverse the ids used by indexer is by using a large number and substracting the id before putting into the index
For example
select id … from …
can be converted to
select 10000000000000-id …from …
and in the app change the id back by
$realid = 10000000000000-$idfromsphinx

When using cutoff we had many queries have really huge performance gains.

Hope this helps others.
If you have an opinion of this, feel free to give.

1 Like