MySQL Query with good execution plan is very slow due to Sending data operation

MySQLoptimizationperformancequery-performance

I have the following query:

SELECT
    a.borrowerId AS borrower_id,
    a.created,
    NULL AS initiator_team_id,
    NULL AS initiator_user_id,
    NULL AS old_team_id,
    NULL AS old_user_id,
    n.teamId AS new_team_id,
    a.lenderUserId AS new_user_id
FROM a
INNER JOIN b ON a.borrowerId = b.id
INNER JOIN n
    ON a.lenderUserId = n.lenderUserId
    AND a.created >= n.created
    AND (a.created < n.deleted OR (n.deleted IS NULL AND n.status = 'active'))
LEFT OUTER JOIN ass ON a.borrowerId = ass.borrower_id AND a.created = ass.created
WHERE a.`type` = 'created'
AND a.deleted IS NULL
AND ass.borrower_id IS NULL;

EXPLAIN presents me with the following plan which looks fine:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  b   ALL NULL    NULL    NULL    NULL    129132  
1   SIMPLE  a   ref type,created,deleted,fk-lenderUser-activities,fk-borrower-activities,idx_type   fk-borrower-activities  4   bi.b.id 3   Using where
1   SIMPLE  n   ref unq_active  unq_active  4   a.lenderUserId  1   Using where
1   SIMPLE  ass eq_ref  PRIMARY PRIMARY 8   a.borrowerId,a.created  1   Using where; Using index; Not exists

But when I run the query it takes ~40 seconds. So I ran the MySQL Profiler and came back with this:

Status  Duration
starting    0.000009
Waiting for query cache lock    0.000004
Waiting on query cache mutex    0.000004
checking query cache for query  0.000063
checking permissions    0.000005
checking permissions    0.000004
checking permissions    0.000004
checking permissions    0.000005
Opening tables  0.000030
System lock 0.000012
init    0.000032
optimizing  0.000019
statistics  0.001302
preparing   0.000023
executing   0.000004
Sending data    40.217320
end 0.000008
query end   0.000006
closing tables  0.000011
freeing items   0.000393
logging slow query  0.000008
logging slow query  0.000005
cleaning up 0.000010

The query actually returns no data with the LEFT JOIN and NULL check present. But removing that check has no effect on the way the query actually executes.

So my question is:

  1. Why is MySQL Sending data when the LEFT JOIN and NULL check prevent there being any rows that match?
  2. How can I rewrite this so that it will actually perform faster?

Best Answer

Saying "Sending data" merely points out how useless the Profiler often is.

This index on a might change the EXPLAIN (eg, start with a instead of b) and speed up things:

INDEX(`type`, `deleted`)  -- in either order

If you need further discussion, please provide SHOW CREATE TABLE and some clues of about how big each table is.

Why is b used? Would you get the 'right' answer if you left out

INNER JOIN b ON a.borrowerId = b.id