does LIMIT have effect before or after JOIN?

Question

It's a simple question - I just found different answers to this so I'm not sure. Let me describe:

If you have a query, like this one:

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

This basically would list the last 10 entries of the logins table, replacing the user_id with the username over a JOIN.

Now my question is, does LIMIT take effect while the JOIN happens (so that it only joins the first 10 entries) or after the JOIN? (Where it would join the whole table, and then cut out the first 10 entries).

I'm asking this because the sample table logins will have many entries - and I'm not sure if a JOIN is too costly performance wise. If LIMIT would only case 10 JOIN's to happen, that wouldn't be a problem.

A second question that came up with this: Is the functionality the same, if a DISTINCT is added? Will it still stop, at 10 entries? And no, this isn't going to be ordered by ORDER BY.

Answer 1

Don't worry. The LIMIT will happen at the same time with the join: MySQL will not read through the entire logins table, but fetch line by line (joining each time on users) until it has found 10 lines.

Do note that if a users.id appears two times in the table, the JOIN will duplicate the logins line and add each users line. The total amount of lines will still be 10, but you'll have 9 logins.