MySQL JOIN in SELECT Causing Performance Issue

Question

Sorry to be long winded but I want to make sure that everyone understand the issue I am having. So please bear with me.

I have 2 tables, ie. Filter_Clients_Invoices_Entries and Filter_Clients_Invoices_Payments.

Please refer to the query and results

SELECT 
   FCIE.InvoiceID, 
   FCIE.EntryID, 
   FCIE.ClientID, 
   CAST(FCIE.AddedOn AS DATE) AS EntryDate, 
   FCIE.ItemTotal, 
   SUM(IFNULL(FCIP.PaymentAmount,0)) AS Payments

FROM Filter_Clients_Invoices_Entries FCIE
LEFT JOIN Filter_Clients_Invoices_Payments FCIP ON FCIP.EntryID = FCIE.EntryID 
AND CAST(FCIP.PaymentDate AS DATE) BETWEEN '2014-10-01' AND '2014-11-30'

   WHERE FCIE.ClientID = 3693
   AND CAST(FCIE.AddedOn AS DATE) BETWEEN '2014-10-01' AND '2014-11-30'
   AND FCIE.Void = 0
   GROUP BY FCIE.EntryID;

The results are as follows

+-----------+---------+----------+------------+-----------+----------+
| InvoiceID | EntryID | ClientID | EntryDate  | ItemTotal | Payments |
+-----------+---------+----------+------------+-----------+----------+
|     31327 |   34180 |     3693 | 2014-10-31 |     63.30 |    33.30 |
|     31327 |   34181 |     3693 | 2014-10-31 |     63.30 |     0.00 |
|     31327 |   34182 |     3693 | 2014-10-31 |     63.30 |     0.00 |
|     31327 |   34183 |     3693 | 2014-10-31 |     63.30 |     0.00 |
|     31327 |   34184 |     3693 | 2014-10-31 |     63.30 |     0.00 |
|     31327 |   34185 |     3693 | 2014-10-31 |     63.30 |     0.00 |
|     31331 |   34187 |     3693 | 2014-11-03 |     15.00 |     0.00 |
|     31332 |   34188 |     3693 | 2014-11-04 |    100.00 |   100.00 |
|     31341 |   34197 |     3693 | 2014-11-10 |     85.00 |     0.00 |
|     31342 |   34198 |     3693 | 2014-11-10 |     85.00 |     0.00 |
|     31343 |   34199 |     3693 | 2014-11-12 |     95.00 |     0.00 |
|     31344 |   34200 |     3693 | 2014-11-13 |     85.00 |     0.00 |
|     31345 |   34201 |     3693 | 2014-11-13 |     80.00 |     0.00 |
|     31346 |   34202 |     3693 | 2014-11-26 |     80.00 |     0.00 |
|     31347 |   34203 |     3693 | 2014-11-26 |     80.00 |     0.00 |
|     31348 |   34204 |     3693 | 2014-11-26 |    100.00 |     0.00 |
+-----------+---------+----------+------------+-----------+----------+
16 rows in set (0.96 sec)

What I wanted to do was to only retrieve item records that has balance, so this is the modified query

SELECT 
    FCIE.InvoiceID, 
    FCIE.EntryID, 
    FCIE.ClientID, 
    CAST(FCIE.AddedOn AS DATE) AS EntryDate, 
    FCIE.ItemTotal, 
    SUM(IFNULL(FCIP.PaymentAmount,0)) AS Payments

FROM Filter_Clients_Invoices_Entries FCIE
LEFT JOIN Filter_Clients_Invoices_Payments FCIP ON FCIP.EntryID = FCIE.EntryID 
AND CAST(FCIP.PaymentDate AS DATE) BETWEEN '2014-10-01' AND '2014-11-30'

    WHERE FCIE.ClientID = 3693
    AND CAST(FCIE.AddedOn AS DATE) BETWEEN '2014-10-01' AND '2014-11-30'
    AND FCIE.Void = 0
    GROUP BY FCIE.EntryID

    HAVING SUM(IFNULL(FCIP.PaymentAmount,0)) < FCIE.ItemTotal;

The new results are as follows

+-----------+---------+----------+------------+-----------+----------+
| InvoiceID | EntryID | ClientID | EntryDate  | ItemTotal | Payments |
+-----------+---------+----------+------------+-----------+----------+
|     31327 |   34180 |     3693 | 2014-10-31 |     63.30 |    33.30 |
|     31327 |   34181 |     3693 | 2014-10-31 |     63.30 |     0.00 |
|     31327 |   34182 |     3693 | 2014-10-31 |     63.30 |     0.00 |
|     31327 |   34183 |     3693 | 2014-10-31 |     63.30 |     0.00 |
|     31327 |   34184 |     3693 | 2014-10-31 |     63.30 |     0.00 |
|     31327 |   34185 |     3693 | 2014-10-31 |     63.30 |     0.00 |
|     31331 |   34187 |     3693 | 2014-11-03 |     15.00 |     0.00 |
|     31341 |   34197 |     3693 | 2014-11-10 |     85.00 |     0.00 |
|     31342 |   34198 |     3693 | 2014-11-10 |     85.00 |     0.00 |
|     31343 |   34199 |     3693 | 2014-11-12 |     95.00 |     0.00 |
|     31344 |   34200 |     3693 | 2014-11-13 |     85.00 |     0.00 |
|     31345 |   34201 |     3693 | 2014-11-13 |     80.00 |     0.00 |
|     31346 |   34202 |     3693 | 2014-11-26 |     80.00 |     0.00 |
|     31347 |   34203 |     3693 | 2014-11-26 |     80.00 |     0.00 |
|     31348 |   34204 |     3693 | 2014-11-26 |    100.00 |     0.00 |
+-----------+---------+----------+------------+-----------+----------+
15 rows in set (0.95 sec)

Although the query works, you can see that it took nearly 1 second to complete the query ... and this is only for 1 client (WHERE ClientID = 3693).

If I were to remove that ClientID filter, it will return me 106 records but takes 58 SECONDS to complete.

So I am asking help from all MySQL guru out there for the following questions

Can the query be optimized to get the same result with faster execution time
Help modify the query to group by InvoiceID with total sum of ItemTotal

Thanks.

Gordon Linoff · Accepted Answer · 2014-11-27 03:58:00Z

1

This is your query:

SELECT FCIE.InvoiceID, FCIE.EntryID, FCIE.ClientID, CAST(FCIE.AddedOn AS DATE) AS EntryDate, FCIE.ItemTotal, 
       SUM(IFNULL(FCIP.PaymentAmount,0)) AS Payments
FROM Filter_Clients_Invoices_Entries FCIE LEFT JOIN
     Filter_Clients_Invoices_Payments FCIP
     ON FCIP.EntryID = FCIE.EntryID AND
        CAST(FCIP.PaymentDate AS DATE) BETWEEN '2014-10-01' AND '2014-11-30'
WHERE FCIE.ClientID = 3693 AND
      CAST(FCIE.AddedOn AS DATE) BETWEEN '2014-10-01' AND '2014-11-30' AND
      FCIE.Void = 0
GROUP BY FCIE.EntryID
HAVING SUM(IFNULL(FCIP.PaymentAmount,0)) < FCIE.ItemTotal;

There are several ways to improve performance. One is to get rid of the outer group by by using a correlated subquery. Then you can fix the conditions on the dates. And finally you can add indexes to each table to improve performance.

The ultimate query is:

SELECT FCIE.InvoiceID, FCIE.EntryID, FCIE.ClientID, CAST(FCIE.AddedOn AS DATE) AS EntryDate, FCIE.ItemTotal, 
       IFNULL((SELECT SUM(FCIP.PaymentAmount) 
               FROM Filter_Clients_Invoices_Payments FCIP
               WHERE FCIP.EntryID = FCIE.EntryID AND
                     FCIP.PaymentDate >=  '2014-10-01' AND
                     FCIP.PaymentDate < date_add('2014-11-30', interval 1 day)
              ), 0) AS Payments
FROM Filter_Clients_Invoices_Entries FCIE
WHERE FCIE.ClientID = 3693 AND
      FCIE.AddedOn >= '2014-10-01' AND
      FCIE.AddedOn < date_add('2014-11-30', interval 1 day) AND
      FCIE.Void = 0
HAVING Payments < FCIE.ItemTotal;

(Note: this use of the HAVING clause is a MySQL extension.)

Next, the indexes that you want are:

Filter_Clients_Invoices_Entries(Void, ClientId, AddedOn)
Filter_Clients_Invoices_Payments(EntryId, PaymentDate, PaymentAmount)

answered Nov 27, 2014 at 3:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

asyadiqin Over a year ago

Prior to Gordon's post, I did review the tables and added indexes the two tables and the same query that took 58 seconds now took 0.04 seconds ... so he is spot-on about the indexes. No disrespect to Gordon but I will leave this open for a day or so to see if anyone else have any other input or improvements. Otherwise, I will select his answer. Thanks Gordon.

Collectives™ on Stack Overflow

MySQL JOIN in SELECT Causing Performance Issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related