How to implement sphinx search for products with dynamic exchange rates?

Question

Sphinx

Good afternoon!
There is an online store with 10 - 20 million products, mysql database. I would like to implement a search on sphinx, but there is a problem with the prices of goods filled in different currencies (rubles, euros, dollars) and sorting by price is implemented. The rate is now changing frequently and I did not want to completely constantly re-index the index when changing the rate. Are there any options for implementing such a scheme in Sphinx?
Now it all works through JOIN in one table goods in another currency value. Everything is displayed on the site in rubles.
Thanks

Answer 1

Good afternoon.
You can implement it like this:
1. In the index, enter only the price in the original unit and the field responsible for the type of currency.
2. We pass the current course to the request to the sphinx:

SELECT 
 (IF(PriceFlag=0,Price*cursDol,IF(PriceFlag=1,Price*cursUE,Price))) AS PriceRub
FROM Index
ORDER BY PriceRub

Or you can create a global server variable (more precisely, for each currency)
SET [INDEX index_name] GLOBAL @user_variable_name = (int_val1 [, int_val2, ...])
And use it in the query:

(Это происходит раз в 10 минут)
SET GLOBAL @cursDol=50
SET GLOBAL @cursUE=70

--Запрос
SELECT 
 (IF(PriceFlag=0,Price*@cursDol,IF(PriceFlag=1,Price*@cursUE,Price))) AS PriceRub
FROM Index
ORDER BY PriceRub