Search code examples
mysqlsqlquery-performance

Improving SQL Query Select Performance

I have a SQL query that pulls data from multiple tables. The only issue I am having is really the query takes to long and I was wondering if there is anyway I can speed it up. I made some minor improvements by using INNER JOIN rather than LEFT JOIN but the query is to slow.

SELECT 
    clientlist.CRMContactId,
    clientlist.ClientAdviser,
    COALESCE(NULLIF(clientlist.FirstName, ""), clientlist.CorporateName) AS FirstName,
    clientlist.LastName,
    clientlist.ServiceStatusName, 
    FORMAT(t.totalfum, 2) AS "Funds Under Management",
    FORMAT(d.totalfci, 2) AS "Total Income", 
    (SELECT DueDate 
     FROM tasks 
     WHERE ClientRef = clientlist.ClientRef 
       AND `Status` <> "Complete" 
     ORDER BY DueDate DESC 
     LIMIT 1) AS NextDate, 
    (SELECT CompletedDate 
     FROM tasks 
     WHERE ClientRef = clientlist.ClientRef 
       AND `Status` = "Complete" 
     ORDER BY DueDate DESC 
     LIMIT 1) AS LastDate 
FROM 
    clientlist
INNER JOIN 
    (SELECT 
         plans.ClientId, SUM(plans.CurrentVal) AS totalfum
     FROM 
         plans
     GROUP BY 
         plans.ClientId) t ON clientlist.CRMContactId = t.ClientId
INNER JOIN 
    (SELECT 
         adviserfci.ClientId, SUM(adviserfci.Payable) AS totalfci
     FROM 
         adviserfci
     WHERE 
         IncomeType IN ("Renewal Commission", "Ongoing Fee", "Fund Based Commission") 
         OR (Incometype = "Payaway Received" 
             AND UnderlyingIncomeType IN ("Renewal", "Ongoing Fee", "Fund Based"))
     GROUP BY 
         adviserfci.ClientId) d ON clientlist.CRMContactId = d.ClientId
WHERE 
    d.totalfci IS NOT NULL

I've also read somewhere the explain command will help determine issues however I don't understand the response.

enter image description here

Is there any way I can increase the performance of this query?

Solution

  • Fold the test for d.totalfci IS NOT NULL into the subquery that generates it, even if it needs to be in a HAVING clause.

    Add some indexes

    tasks:  INDEX(ClientRef, `Status`, DueDate)
plans:  INDEX(ClientId, CurrentVal)
adviserfci:  INDEX(ClientId)