[SQL] MySQL left join produces less optimal query

My users table lists all users:

id name
1 alice
2 bob

My users_teams table defines which users are in which teams.

id team_id user_id
1 100 1
2 100 2

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

select_type table type rows extra
SIMPLE users index 10 Using where

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

select_type table type rows extra
SIMPLE u ALL 999999 Using where; Using temporary; Using filesort
SIMPLE t ALL 1234 Using where; Using join buffer (flat, BNL join)

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?

Continue reading...