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:
- Why is MySQL
Sending data
when theLEFT JOIN
andNULL
check prevent there being any rows that match? - 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 theEXPLAIN
(eg, start witha
instead ofb
) and speed up things: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