MySQL Forums
Forum List  »  General

Slow query: getting the closest date to a certain date
Posted by: André Næss
Date: April 03, 2006 06:07AM

I have a table holding transactions. Each transaction may be in a currency different from €, and I need to convert all the prices to euros. I have a table with the currency rates for all dates from 1999-01-01 until today.

The problem is that the date of the transaction may be a Saturday or a Sunday, or some other day on which no conversion rate exists. In this case the rule is to look backwards in time for the most recent date. The query for this looks like this:

SELECT contract.contract_id,
(SELECT MAX(rate.date)
FROM currency_eurorate rate
WHERE rate.date <= contract.contract_date) AS currency_date
FROM contract

The result is a table which maps each contract_id to the currency date applying to that transactions (transactions are called contracts for historical reasons).

This query is slow. It currently executes in about 50 seconds on my computer (faster on the server), but will get slower and slower as more and more contracts are added. I've currently implemented it as a sort of "view". A cron job recreates this table every night, which is good enough for a while, but not a solution I'm very happy with.

One other option is to "fill in" the currency table, making sure that it has entries for all dates so that a simple join will do. But are there other, more clever solutions out there? (Using MySQL 4.1)

Options: ReplyQuote


Subject
Written By
Posted
Slow query: getting the closest date to a certain date
April 03, 2006 06:07AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.