I need to simplify a MySQL sub query for performance - please help

Posted by Richard on Stack Overflow See other posts from Stack Overflow or by Richard
Published on 2010-05-31T23:23:41Z Indexed on 2010/05/31 23:33 UTC
Read the original article Hit count: 222

Filed under:
|

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.

© Stack Overflow or respective owner

Related posts about mysql

Related posts about mysql-query