Does LIMIT apply before or after a JOIN?

Question

This is a simple question - I just found different answers to this, so I'm not sure. let me describe:

If you have a request like this:

SELECT logins.timestamp, users.name FROM logins LEFT JOIN users ON users.id = logins.user_id LIMIT 10 

Basically this will list the last 10 entries in the login table, replacing user_id with the username above the JOIN.

Now my question is: is LIMIT applied during the join (so that it only connects to the first 10 elements) or after the join? (where he would join the whole table and then cut out the first 10 entries)

I ask about this because there will be many entries in the sample logins table - and I'm not sure if the connection is too expensive. If LIMIT executes only case 10 JOIN, this will not be a problem.

The second question that arose because of this: Is the functionality the same if DISTINCT is added? Will he still dwell on 10 entries? And no, this will not be ordered ORDER BY

Answer 1

Do not worry. LIMIT will occur simultaneously with the connection: MySQL will not read the entire login table, but displays line by line (each time it joins users) until it finds 10 lines.

Note that if the user.id file appears twice in the table, the JOIN will duplicate the login line and add each user line. The total number of lines will be 10, but you will have 9 logins.