Slow MySQL query, EXPLAIN shows Using temporary; Using filesort

Question

This query:

EXPLAIN SELECT ppi_loan.customerID,
               loan_number,
               CONCAT(forename, ' ', surname) AS agent,
               name,
               broker,
              (SELECT timestamp
               FROM ppi_sar_status
               WHERE history = 0
                   AND (status = 10 || status = 13)
                   AND ppi_sar_status.loanID = ppi_loan.loanID) AS ppi_unsure_date,
              fosSent,
              letterSent,
              (SELECT timestamp
               FROM ppi_ques_status
               WHERE status = 1 
                   AND ppi_ques_status.loanID = ppi_loan.loanID
               ORDER BY timestamp DESC LIMIT 1) AS sent_date,
               ppi_ques_status.timestamp
FROM ppi_loan
LEFT JOIN ppi_assignments ON ppi_assignments.customerID = ppi_loan.customerID
LEFT JOIN italk.users ON italk.users.id = agentID
LEFT JOIN ppi_ques_status ON ppi_ques_status.loanID = ppi_loan.loanID
JOIN ppi_lenders ON ppi_lenders.id = ppi_loan.lender
JOIN ppi_status ON ppi_status.customerID = ppi_loan.customerID
JOIN ppi_statuses ON ppi_statuses.status = ppi_status.status
   AND ppi_ques_status.status = 1
   AND ppi_ques_status.history = 0
   AND (cc_type = '' || (cc_type != '' AND cc_accepted = 'no'))
   AND ppi_loan.deleted = 'no'
   AND ppi_loan.customerID != 10
GROUP BY ppi_loan.customerID, loan_number

Is very slow, here are all the results from the EXPLAIN query

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY ppi_ques_status ref loanID,status,history   status  3   const   91086   Using where; Using temporary; Using filesort
1   PRIMARY ppi_loan    eq_ref  PRIMARY,customerID  PRIMARY 8   ppimm.ppi_ques_status.loanID    1   Using where
1   PRIMARY ppi_lenders eq_ref  PRIMARY PRIMARY 4   ppimm.ppi_loan.lender   1   Using where
1   PRIMARY ppi_assignments eq_ref  customerID  customerID  8   ppimm.ppi_loan.customerID   1   
1   PRIMARY users   eq_ref  PRIMARY PRIMARY 8   ppimm.ppi_assignments.agentID   1   
1   PRIMARY ppi_status  ref status,customerID   customerID  8   ppimm.ppi_loan.customerID   6   
1   PRIMARY ppi_statuses    eq_ref  PRIMARY PRIMARY 4   ppimm.ppi_status.status 1   Using where; Using index
3   DEPENDENT SUBQUERY  ppi_ques_status ref loanID,status   loanID  8   func    1   Using where; Using filesort
2   DEPENDENT SUBQUERY  ppi_sar_status  ref loanID,status,history   loanID  8   func    2   Using where

Why is it scanning so many rows and why "Using temporary; Using filesort"? I can't remove any subqueries as I need all of the results that they produce

Answer 1

As already mentioned in a comment, the main cause of a slow query is that you seem to have single column indexes only, while you would need multi-column indexes to cover the joins, the filters, and the group by.

Also, your query has 2 other issues:

1. Even though you group by on 2 fields only, several other fields are listed in the select list without being subject to an aggregate function, such as min(). MySQL does allow such queries to be run under certain sql mode settings, but they are still against the sql standard and may have unexpected side effects, unless you really know what your are doing.

2. You have filters on the ppi_loan table in the join condition that is the left table in a left join. Due to the nature of the left join, these records will not be eliminated from the resultset, but MySQL will not join any values on them. These criteria should be moved to the where clause.

The indexes I would create:

• ppi_sar_status: multi-column index on loanID, status, history fields - I would consider moving this to the join section because this table is not there

• ppi_ques_status: multi-column index on loanID, status, timestamp fields - this would support both the subquery and the join. Remember, the subquery also has filesort in the explain.

• ppi_loan: as a minimum a multi-column index on customerID, loan_number fields to support the group by clause, therefore avoiding the filesort as a minimum. You may consider adding the other fields in the join criteria based on their selectivity to this index.

I'm also not sure why you have the last 2 status tables in the join, since you are not retrieving any values from them. If you re using these tables to eliminate certain records, then consider using an exists() subquery instead of a join. In a join MySQL needs to fetch data from all joined tables, whereas in an exists() subquery it would only check if at least 1 record exists in the resultset without retrieving any actual data from the underlying tables.