Laravel Eloquent Making Query Execution Slow

i am using laravel eloquent relationships and having slow query execution issue let say i have two table chats(1 Million Records)
leads(half Million Records)
and i want to query chats & lead data of a group where using

 lead::select("chat_lead_id")->with([chats=>function($q){      $q->select('chat_id',"group_id")->where("group_id"=>1)      })  

Query Debug First Query :

result =select chat_lead_id from leads   

Second Query

select chat_id,group_id from chats  where group_id =1 AND chat_id in (result)  

please check above debug you can see that First Query will fetch half million rows from database which will take to much time so what should i do move to join query ?? when using WhereHas it is placing inner query which is taking time also Query With WhereHas

select * from `gc_od_leads`      where exists (select * from `gc_od_chat`              where `gc_od_leads`.`leads_chat_id`                  = `gc_od_chat`.`chat_id`                and `chat_group_id` = ?)  

https://stackoverflow.com/questions/66816662/laravel-eloquent-making-query-execution-slow March 26, 2021 at 08:08PM