I have a query which gets a customer and the latest transaction for that customer. Currently this query takes over 45 seconds for 1000 records. This is especially problematic because the script itself may need to be executed as frequently as once per minute!

I believe using subqueries may be the answer, but I've had trouble constructing it to actually give me the results I need.

SELECT
    customer.CustID,
    customer.leadid,
    customer.Email,
    customer.FirstName,
    customer.LastName,
    transaction.*,
    MAX(transaction.TransDate) AS LastTransDate
FROM customer
INNER JOIN transaction ON transaction.CustID = customer.CustID 
WHERE customer.Email = '".$email."'
GROUP BY customer.CustID
ORDER BY LastTransDate
LIMIT 1000

I really need to get this figured out ASAP. Any help would be greatly appreciated!