CODEWITHSUNDEEP
mysqlsqlperformancejoinselect
Timmy Balk
Timmy Balk

Reputation: 238

Does JOIN or LEFT JOIN keep checking in a SELECT query?

I have a JOIN query but I need to optimize it for performance.

For example, in this query:

"SELECT id FROM users WHERE id = :id"

Since there is no LIMIT 1 at the end of the query, that select query will keep searching. If I add LIMIT 1 to the end of that query, it will select only one and stop searching for more.

Here is my question and query:

"SELECT messages.text, users.name
FROM messages
LEFT JOIN users
ON messages.from_id = users.id
WHERE messages.user_id = :user_id"

In the JOIN users ON messages.from_id = users.id part, since there is only 1 user with that ID, will it keep searching after it has found that query? If it does, how can I optimize it so that it only searches for 1 row?

Upvotes: 0

Views: 53

Answers (1)

Rick James
Rick James

Reputation: 142433

SELECT id FROM users WHERE id = :id

If there is no index on id, the entire table is scanned.

If there is a UNIQUE or PRIMARY KEY on id, only one row will be checked.

If there is a plain INDEX, it will scan from the first match until it finds an id that does not match.

For this:

SELECT  m.text, u.name
    FROM  messages AS m
    LEFT JOIN  users AS u  ON m.from_id = u.id
    WHERE  m.user_id = :user_id

It will do a "Nested Loop Join":

  1. Find the occurrence(s) in messages that satisfy m.user_id = :user_id (see above).
  2. For each such row, reach into users based on the ON clause.
  3. There may be multiple rows (again, depending the index or lack of such).

So, your question "how can I optimize it so that it only searches for 1 row" is answered:

  • If there can only be one row, declare it UNIQUE.
  • If there are sometimes more than on, then INDEX. But don't worry about checking for an extra row; it is not that costly.

You say "only 1 user with that ID", but fail to specify which id in which table.

But that is not the end of the story...

LEFT JOIN may get turned into JOIN. In that case, users may be the first table to look at. Note also that the Optimizer is smart enough to deduce that you want u.id = :user_id. Anyway, the NLJ will start with users, then reach into messages. Again, the types of indexes are important.

Please provide SHOW CREATE TABLE for both tables. Then I can condense the answer to the relevant parts. Please provide EXPLAIN SELECT ... for confirmation of what I am saying.

Upvotes: 1

Related Questions