All my tables use InnoDB, and I have set sphinx sql_range_step to the minimum, which is 128. This improved the performance a lot, but it is still very slow if you make a request right after a new step begins.
I'm sure it would work just fine if I could reduce the range step to 10 or something, but someone found that the min value is hardcoded and there is no way to change it (other than editing the source).
So I was wondering if there was a way to deal with this directly from MySQL. When I am indexing a database, the other databases aren't affected, so it's not the whole server which has been slowed down, but only the database I am indexing.
Is there a way to give less priority to a user or query, or something?
At first, try to optimize your SQL queries sql_query and sql_query_range. Also, you can throttle queries while indexing via sql_ranged_throttle. For example, set it to 1000, to get 1 second (1000 ms) delay before each ranged query.