Return to Question

Post-edit: I did try to use a subquery, it failed spectacularly but probably because of a mistake from my side. Subquery seems to be the best way to solve this, so I've moved it from the question side into an answer.

It seems quite easy to fix this through a subquery:

SELECT
    transaction.*,
(SELECT rate
 FROM rate
 WHERE rate.timestamp<transaction.timestamp and rate.currency=transaction.currency
 ORDER by created_at DESC
 LIMIT 1) as rate
FROM transaction;

The query above is lightning fast if I add "WHERE transaction.id=5" to the query, but it grinds to a halt if I add "WHERE transaction.id<=5", even though it should also be fast.

It seems trivial to fix this by making a loop in an external script, but is it at all possible to do this in a scalable way through a clean SQL query in MySQL?