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?
You need to sign in to view this answers
Leave feedback about this