A simple MySQL query takes 45 seconds (gets the record and its "last" child record)

Question

I have a request that receives a client and the most recent transaction for this client. This query currently takes more than 45 seconds for 1000 records. This is especially problematic because the script itself can run as often as once per minute!

I believe that using subqueries may be the answer, but I had trouble creating it to 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 find out as soon as possible. Any help would be greatly appreciated!

Answer 1

, transaction.CustID, customer.Email.

, customer.CustID , .

:

CREATE INDEX ix_transaction_CustID ON transaction(CustID);
CREATE INDEX ix_customer_Email ON customer(Email);

, EXPLAIN, , .