I have a normal sized mysql table (~100k records), and a prety simple select query.
The main problem is that I have to order my resultset by a calculated value (eur_price), and according to mysql-faq (http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
) I should index the order by coloumn, which - being calculated - cannot be done.
Because of that, mysql uses filesort ordering my resultset, which can take up to 40sec, which I cannot afford.
I cannot use temporary tables, extra fields, I have only read privilige on tha database.
Any workaround making faster my query should help.
SELECT sales.id, sales.price/conversion.rate AS eur_price
LEFT JOIN conversion ON ( sales.currency = conversion.curr_destination )
WHERE sales.country = 'hu'
AND conversion.curr_source = 'eur'
ORDER BY eur_price ASC LIMIT 100