How to achieve good performance in Sphinx?

Question

SQL

Good afternoon!
There is a mechanism that uses the index in sphinx for non-full text searches. That is, in fact it is just a table with a certain number of simple fields (only int).
A fairly simple query with an elementary filter on this plate gave a rather weak result, which caused some surprise. In the process of finding a problem, the query was simplified to a state where there is nowhere to simplify it further and still the query execution time is fantastic:

select user_id from index limit 1;
/* Affected rows: 0  Найденные строки: 1  Предупреждения: 0  Длительность  1 query: 0,110 sec. */

Previously, a different version of this sample ran in a reasonable amount of time because it included the OPTION cutoff=X option. If this setting is present in the request, then the request is executed at lightning speed for 0,(0)1.
In my case, I cannot use this option (or rather, I can, but I don’t know how), because I use reverse sorting by field. And by experience, I found out that if I specify ORDER BY in a query to an index, then the index first selects records from the index according to the conditions from WHERE, then cuts this selection to the length specified in cutoff, and sorts the remaining records by the value specified in ORDER BY. It works lightning fast, but it doesn't suit me at all. According to my observation, selection without specifying ORDER BY happens with sorting by id, but I need ORDER BY id DESC.
Let me give you an example:
Suppose we have 1000 records in the table with id from 1 to 1000. If I do not specify ORDER BY, but specify cutoff, then it sorts by id and I get the correct result.
If I need ORDER BY id DESC LIMIT 10 OPTION cutoff=10, then first the index, focusing on cutoff, will take the first 10 records (id from 1 to 10), and then sort them in reverse order and return this result to me.
I had the first version that the index is built according to the wrong sort, but I double-checked the config and made sure that both the index itself and its delta index in my sql_query contain the correct sort.
Accordingly, I cannot select the data according to the sorting I need in a reasonable time.
Thanks in advance for your replies!

Answer 1

I can assume that there is a fullscan of the index, because sphinx, when filtering by attributes, without searching, will look through the entire index, calculate all the values ​​that fall under the condition, sort, impose limits and return the result. Because after each request, you can get meta information. Thanks to this mechanism, it is possible to display pagination without an additional count (*) request. And yes, it seems to me that you are using the tool for the wrong case. If you still really need to, you have to put up with it.

Answer 2

well, you are using the tool for other purposes, such things will work with the correct indexes in normal mysql