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

Question

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.

Answer 1

You could try using two queries instead of one, i.e. getting the ID and using that instead of a sub query. This may tell you which query is slow. Also I believe MySQL will cache the separate queries, speeding up multiple requests.

Do you have indexes on the tables? in general you should index any columns that you filter or sort on. In your example that would be the dealer_accounts.date column certainly.

I'm assuming the ID fields are primary keys, if so, it's pointless adding dealer_accounts.id to the sorting criteria.

Answer 2

I'm not sure why you can't just do this:

SELECT 
   dealers.name, dealers.companyName, dealer_accounts.balance
FROM 
   dealers, dealer_accounts
WHERE 
  WHERE dealer_accounts.dealer_id = dealers.id
  AND dealer_accounts.date < '2010-03-30'
ORDER BY dealer_accounts.date DESC, dealer_accounts.id DESC

Try MySQL EXPLAIN PLAN on your query. If you see TABLE SCAN, that's why your query is so slow.

I don't know what indexes you have on your tables, but adding appropriate ones might help. EXPLAIN PLAN could tell you where they're lacking.

Answer 3

Your subquery is run a lot of times, and I suspect that you don't have indices on dealer_accounts.date and dealer_accounts.dealer_id. You should certainly look at the need for filtering on a static field in your subquery. It should be possible to filter on the date once, and then do the subquery on a smaller set.