I need to simplify a MySQL sub query for performance - please help
- by Richard
I have the following query which is takin 3 seconds on a table of 1500 rows, does someone know how to simplify it?
SELECT dealers.name, dealers.companyName, dealer_accounts.balance
FROM dealers
INNER JOIN dealer_accounts
ON dealers.id = dealer_accounts.dealer_id
WHERE dealer_accounts.id = (
SELECT id
FROM dealer_accounts
WHERE dealer_accounts.dealer_id = dealers.id
AND dealer_accounts.date < '2010-03-30'
ORDER BY dealer_accounts.date DESC, dealer_accounts.id DESC
LIMIT 1
)
ORDER BY dealers.name
I need the latest dealer_accounts record for each dealer by a certain date with the join on the dealer_id field on the dealer_accounts table. This really should be simple, I don't know why I am struggling to find something.
