Simple MySQL Query taking 45 seconds (Gets a record and its "latest" child record)

I have a query which gets a customer and the latest transaction for that customer. Currently th开发者_开发知识库is 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!

Make sure you have an index for transaction.CustID, and another one for customer.Email.

Assuming customer.CustID is a primary key, this should already be indexed.

You can create an index as follows:

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

As suggested in the comments, you can use the EXPLAIN command to understand if the query is using indexes correctly.