Larvel query builder query taking 20 seconds to execute

Question

I have a Laravel query builder query which is taking more than 20 seconds to retrieve ~2500 records. Ideally it should take much less time. I think the orOn join in my query is what is causing this issue. Am not able to figure out how can I optimize this query to execute much more quickly

DB::table('customer as c')->leftJoin('feedback as fr', 
                    function($leftJoin){
                        $leftJoin->on('c.active_feedback_req_id_1','=','fr.id')
                        ->orOn('c.active_feedback_req_id_2','=', 'fr.id') 
                        ->orOn('c.active_feedback_req_id_3','=', 'fr.id'); 
                    })
                    ->leftJoin('feedback_request_status as frs', 'fr.feedback_request_status_id', 'frs.id')
                    ->leftJoin('customer_service as cs', 'c.id', 'cs.customer_id')
                    ->where('c.business_location_id', $request->location_id)
                    ->where('c.is_active', 1)
                    ->select('c.id as customer_id','c.name as customer_name','c.created_at as customer_creation_date','c.email as customer_email', 'c.phone as customer_phone','c.phone_extension as customer_phone_extension','c.whatsApp as customer_whatsApp','c.whatsApp_phone_code as customer_whatsApp_extension','active_feedback_req_id_email as email_req','active_feedback_req_id_sms as sms_req','active_feedback_req_id_whatsApp as whatsApp_req','fr.service_taken as service_taken','fr.service_provider as service_provider', 'fr.reminders_sent as reminders_sent', 'frs.status as status','frs.color_code as color_code', 'c.is_active as active_status', 'c.is_unsubscribed as is_unsubscribed','cs.service_taken as customer_service_taken', 'cs.service_provider as customer_service_provider')
                    ->orderBy('c.created_at','DESC')
                    ->groupBy('c.id')
                    ->get();

Please let me know if anyone can help with this.

Have you run the query as straight up SQL in your terminal/db gui app? Building/modifying the query there usually helps me in situations like these. Another option could be to index the columns you are referencing, this can speedup the query times at the expense of storage. — Fact0Log
– Fact0Log, Commented Oct 5, 2023 at 10:20
im guessing active_feedback_req_id_x columns dont have an index? — mrhn
– mrhn, Commented Oct 5, 2023 at 10:39
@Fact0Log I have not tried converting the query to raw SQL. But I have tried indexing the fields and it still is taking 20 seconds. I will need to see how to convert this query to pure SQL. Please let me know if you are aware of a way to do this. Thanks — HarshGaikwad
– HarshGaikwad, Commented Oct 5, 2023 at 11:29
you can replace that ->get() with ->toSql() and see the query being generated. anyway, too many joins may cause query to be slow - even worse compounded with missing indexes. oh, column sizes do matter at times. — Bagus Tesa
– Bagus Tesa, Commented Oct 5, 2023 at 11:35

Ankit Shekhawat · Accepted Answer · 2023-10-11 06:17:29Z

0

I think for this you can use eager loading. For all of these left joins, you can you define relationship functions inside your customer module.

I think you should treat them as separate relationships and call them like these.

Customer::with('getActiveFeedback1','getActiveFeedback2','getActiveFeedback3','getFeedbackRequestStatus','getCustomerService','getbusinesslocation')->where('is_active',1)->orderBy('created_at','DESC')->groupBy('id')->get();

edited Oct 11, 2023 at 6:17

answered Oct 5, 2023 at 12:23

Ankit Shekhawat

12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Larvel query builder query taking 20 seconds to execute

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related