3

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!

Brian Lacy
  • 18,785
  • 10
  • 55
  • 73
  • 1
    run on mysql the query "EXPLAIN your_query_here;" the results might help you. – clyfe Mar 13 '10 at 00:09
  • Try running EXPLAIN on the actual server and posting the results. – Mads Ravn Mar 13 '10 at 00:11
  • Yes, please show EXPLAIN and the table structure. I also suggest reading http://stackoverflow.com/questions/1204402/how-do-i-ask-for-help-optimizing-fixing-queries-in-mysql – hobodave Mar 13 '10 at 00:12
  • @clyfe: Apparently we agree. @Brian Lacy: See http://dev.mysql.com/doc/refman/5.1/en/using-explain.html for an explation of output (change 5.1 to relevant server version). – Mads Ravn Mar 13 '10 at 00:13
  • I think you may have a bigger problem. The transaction.* is going to return random values from some transaction record for the customer but not necessarily the ones from the record with the maximum transaction date. Is that what you want? – Larry Lustig Mar 13 '10 at 00:21
  • @Larry Lustig: I was just pondering the same. I could see it working as intended though, as transaction with max(date) probably has highest id. But it is relying on unspecified behavior. – Mads Ravn Mar 13 '10 at 00:37

1 Answers1

2

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.

Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443