1

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?
  • Sending data: The thread is reading and processing rows for a SELECT statement, and sending data to the client. – BK435 Jul 30 '15 at 21:51
  • @BK435 Why would the server be sending data to the client in this case? When the client renders my result set, there are 0 rows. That's the part that doesn't make sense. – Noah Goodrich Jul 30 '15 at 21:53
  • @BK435 - No. It is returning exactly what I expect. The poor performance of this query is what is unexpected. – Noah Goodrich Jul 30 '15 at 22:13
  • @NoahGoodrich You're missing an index on b.id – tombom Jul 31 '15 at 7:31
  • @NoahGoodrich "Sending data" is a misleading name as explained in this answer - stackoverflow.com/a/10347999 - it means your query plan actually is not that good. – jkavalik Jul 31 '15 at 8:39
2

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
| improve this answer | |
1

@NoahGoodrich, many people wonder the same thing, and the critical clue lies in understanding how a LEFT or RIGHT JOIN functions differently than an INNER JOIN or an OUTER JOIN. Because a LEFT join means to return everything in the left table that matches the query, and only the things in the joined table that match, the way it's executed is to take each row of the LEFT table and process the query against all other joined tables. Then go back and repeat with the next row of the LEFT table. So, how ever many rows are in the LEFT table, is how many times the entire query is executed over and over again. And if you are joining more than two tables, the iterations increase exponentially as they have to run for each permutation of each successive table join. On the other hand, an INNER or just a plain JOIN executes the same query one time.

The only practical solution is to redesign your table fields (denormalize) and start repeating key common fields in more than one table, so that you can eliminate left and right joins of large tables. If only a few hundred rows, performance won't be an issue.

A second possible improvement that only works if this are primarily reporting tables and not dynamic transaction processing tables, is to create maximum indexes to try and make the result able to use indexed columns as much as possible. But most of the time it causes little improvement (10 to 20% max) because of how your query must work.

As far as the profiler comment of "Sending data", as others said what's going on is multiple things. It's actually 'preparing AND sending data', but mostly preparing. That means it's really executing the query, populating the temporary table of results, etc. The previous "executing" of only 0.000004 seconds is really 'giving permission for the database engine to start execution', not the actual entire execution of the query itself.

Another tidbit comment, LEFT JOIN and LEFT OUTER JOIN are the same thing, but either works the same in the execution of your query.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.