MySQL JOIN searching too many rows

Question

I have the a query that brings out 8,566 results. But the EXPLAIN shows one table searching 233,190 rows and I can't see why. It shows the problem table (ppi_sar_status) using the 'status' key, but the query joins to that table on a differnt key 'loanID'

View the EXPLAIN results

SELECT DISTINCT 
      ppi_loan.loanID, 
      ppi_loan.lender, 
      ppi_loan.customerID, 
      ppi_loan.agreementNo, 
      loan_number, 
      ppi_lenders.name, 
      ppi_status.status, 
      ppi_statuses.description, 
      ppi_loan.broker, 
      (SELECT sarSent 
            FROM ppi_sar 
            WHERE ppi_sar.customerID = ppi_loan.customerID 
                AND ppi_sar.lender = ppi_loan.lender 
            ORDER BY sarSent DESC 
            LIMIT 1) as sarSent, 
      (SELECT COUNT(DISTINCT(groupID)) 
            FROM ppi_mdrs 
            WHERE ppi_mdrs.customerIDfk = ppi_loan.customerID 
                AND ppi_mdrs.lender = ppi_loan.lender 
                AND sent_to_lender = '0000-00-00 00:00:00') AS mdrs_sent, 
      (SELECT sent 
            FROM ppi_mdrs 
            WHERE ppi_mdrs.customerIDfk = ppi_loan.customerID 
                AND ppi_mdrs.lender = ppi_loan.lender 
            ORDER BY sent DESC 
            LIMIT 1) AS mdr_last_sent,
      mobilePhone, 
      homePhone,
      title, 
      firstName, 
      lastName, 
      loaSent 
   FROM 
      ppi_loan 
         JOIN ppi_sar_status 
            ON ppi_loan.loanID = ppi_sar_status.loanID
         JOIN ppi_customer 
            ON ppi_loan.customerID = ppi_customer.customerID
         JOIN ppi_lenders 
            ON ppi_loan.lender = ppi_lenders.id
         JOIN ppi_status 
            ON ppi_loan.customerID = ppi_status.customerID
            JOIN ppi_statuses 
               ON ppi_status.status = ppi_statuses.status
         LEFT JOIN ppi_mdrs 
            ON ppi_loan.customerID = customerIDfk  
            AND ppi_loan.lender = ppi_mdrs.lender
   WHERE 
          ppi_loan.customerID != 10 
      AND ppi_status.status != 9 
      AND ppi_status.status != 32 
      AND ppi_status.status != 54 
      AND ppi_status.status != 58 
      AND ppi_status.status != 59 
      AND ppi_status.status != 61 
      AND ppi_status.status != 69 
      AND ppi_status.status != 60 
      AND ppi_status.history = 0 
      AND ppi_loan.customerID 
         IN (SELECT customerID 
                FROM ppi_status 
                WHERE (status = 5 || status = 6 || status = 79) 
                  AND timestamp > '2015-04-01' 
                  AND ppi_status.customerID = ppi_loan.customerID)
      AND ppi_sar_status.status = 16 
      AND ppi_sar_status.history = 0 
      AND (cc_type = '' || (cc_type != '' AND cc_accepted = 'no'))
      AND ppi_loan.deleted = 'no'

Answer 1

It's due to the WHERE clause:

ppi_sar_status.status = 16

I'll probably get a lot of flack for saying this but maybe you should try using an index hint. https://dev.mysql.com/doc/refman/5.7/en/index-hints.html

(But don't forget to read the caveats http://www.mysqldiary.com/the-battle-between-force-index-and-the-query-optimizer/)

Answer 2

After cleaning the readability of your query and posting the edit, I saw the "||" components which is typically associated with a logical OR in languages. However, in MySQL, appears to represent that of concatenating values such as strings.

In the later part of your WHERE clause...

 WHERE (status = 5 || status = 6 || status = 79) 
                  AND timestamp > '2015-04-01' 
                  AND ppi_status.customerID = ppi_loan.customerID)
      AND ppi_sar_status.status = 16 
      AND ppi_sar_status.history = 0 
      AND (cc_type = '' || (cc_type != '' AND cc_accepted = 'no'))

it looks like you SHOULD have logical "OR" such as

 WHERE (status = 5 OR status = 6 OR status = 79) 

and also..

      AND (cc_type = '' OR (cc_type != '' AND cc_accepted = 'no'))

REVISION TO ANSWER

I dont know your explicit indexes, but to have covering indexes and help optimize this query further, I would suggest the following indexes on your respective tables.

table           index
ppi_sar         ( customerID, lender )  *or lender,customerid either way*
ppi_mdrs        ( customerIdfk, lender, sent_to_lender ) *or lender,customerid,sent...*
ppi_sar_status  ( loanid, status, history )
ppi_status      ( customerID, status, history, timestamp )   *timestamp optional*

But, now with all that, I would also do one more thing. MySQL has a special keyword "STRAIGHT_JOIN" that tells MySQL to query in the order I told you, dont think for me. As it SOUNDS, it is trying to your your statuses table as the primary. But since your true driving table is that of loans, and first in your FROM clause, that SHOULD be the basis of the query. so I would change to

SELECT STRAIGHT_JOIN DISTINCT ... rest of query