MySQL left join produces less optimal query

My users table lists all users:

My users_teams table defines which users are in which teams.

I want to search for users with a certain name, and also know which team they are on.

I select the users first, which is a simple index query with 10 rows:

EXPLAIN SELECT * FROM users
WHERE name LIKE "%bob%"
ORDER BY id DESC LIMIT 10

But if I try a LEFT JOIN, the query explanation looks much worse:

EXPLAIN SELECT u.id, t.team_id FROM users u
LEFT JOIN users_teams t ON u.id = t.user_id
WHERE u.name LIKE "%bob%"
ORDER BY u.id DESC LIMIT 10

I want to just fetch these 10 rows, then for each row know in which team this user is (if at all). What is the optimal query to do that?

You need to sign in to view this answers