Bad ways of using whereIn query
Draft Disclaimer: Please note that this article is currently in draft form and may undergo revisions before final publication. The content, including information, opinions, and recommendations, is subject to change and may not represent the final version. We appreciate your understanding and patience as we work to refine and improve the quality of this article. Your feedback is valuable in shaping the final release.
Language Mismatch Disclaimer: Please be aware that the language of this article may not match the language settings of your browser or device.
Do you want to read articles in English instead ?
- replaced fully fetched queries into one with sub query
- sub query still not the highest performing query but better the above
->whereIn('id', function ($query) {
$query->select('team_id')
->from('team_members');
})
Before 400k rows analyzed
select distinct `users`.*
from `users`
where `id` in
(select `user_id`
from `team_members`
where `team_members`.`team_id` in (?, ?, ?)
)
and `users`.`deleted_at` is null
After After 19k rows
select distinct `users`.*
from `users`
where `id` in
(select `user_id`
from `team_members`
where `team_members`.`team_id` in
(select `teams`.`id`
from `teams`
where exists
(select *
from `users`
inner join `team_members` on `users`.`id` = `team_members`.`user_id`
where `teams`.`id` = `team_members`.`team_id`
and `user_id` = ? and `users`.`deleted_at` is null
) and `teams`.`deleted_at` is null)
)
and `users`.`deleted_at` is null
explain
[
{
"id": 1,
"select_type": "SIMPLE",
"table": "team_members",
"partitions": null,
"type": "range",
"possible_keys": "team_members_team_id_sub_team_id_user_id_unique,team_members_team_id_index,team_members_user_id_index",
"key": "team_members_team_id_sub_team_id_user_id_unique",
"key_len": "4",
"ref": null,
"rows": 386448,
"filtered": 100,
"Extra": "Using where; Using index; Using temporary"
}
]
explain
[
{
"id": 1,
"select_type": "PRIMARY",
"table": "users",
"partitions": null,
"type": "ALL",
"possible_keys": "PRIMARY",
"key": null,
"key_len": null,
"ref": null,
"rows": 15456,
"filtered": 10,
"Extra": "Using where; Using temporary"
},
{
"id": 1,
"select_type": "PRIMARY",
"table": "<subquery2>",
"partitions": null,
"type": "eq_ref",
"possible_keys": "<auto_key>",
"key": "<auto_key>",
"key_len": "4",
"ref": "dreamwell_production.users.id",
"rows": 1,
"filtered": 100,
"Extra": "Distinct"
},
{
"id": 2,
"select_type": "MATERIALIZED",
"table": "teams",
"partitions": null,
"type": "ALL",
"possible_keys": "PRIMARY",
"key": null,
"key_len": null,
"ref": null,
"rows": 1863,
"filtered": 10,
"Extra": "Using where; Distinct"
},
{
"id": 2,
"select_type": "MATERIALIZED",
"table": "team_members",
"partitions": null,
"type": "ref",
"possible_keys": "team_members_team_id_sub_team_id_user_id_unique,team_members_team_id_index,team_members_user_id_index",
"key": "team_members_team_id_sub_team_id_user_id_unique",
"key_len": "4",
"ref": "dreamwell_production.teams.id",
"rows": 664,
"filtered": 100,
"Extra": "Using index; Distinct"
},
{
"id": 4,
"select_type": "DEPENDENT SUBQUERY",
"table": "team_members",
"partitions": null,
"type": "ref",
"possible_keys": "team_members_team_id_sub_team_id_user_id_unique,team_members_team_id_index,team_members_user_id_index",
"key": "team_members_team_id_sub_team_id_user_id_unique",
"key_len": "4",
"ref": "dreamwell_production.teams.id",
"rows": 664,
"filtered": 100,
"Extra": "Using index"
},
{
"id": 4,
"select_type": "DEPENDENT SUBQUERY",
"table": "users",
"partitions": null,
"type": "eq_ref",
"possible_keys": "PRIMARY",
"key": "PRIMARY",
"key_len": "4",
"ref": "dreamwell_production.team_members.user_id",
"rows": 1,
"filtered": 10,
"Extra": "Using where"
}
]
Not the best solution, but better than previous
