How to optmize the query if it has multiple joins and the table is large?

please next time wrap your code with 3 backticks code

$results = DB::table('tblencounter') ->join('tblpatientinfo', 'tblpatientinfo.fldpatientval', '=', 'tblencounter.fldpatientval') ->join('tblpatbilling as bd', 'bd.fldencounterval', '=', 'tblencounter.fldencounterval') ->whereRaw('cast(bd.fldtime as date) >= ?', [$fromdate]) ->whereRaw('cast(bd.fldtime as date) <= ?', [$todate]) ->whereIn('bd.fldcomp', ['comp57', 'comp51', 'comp52']) ->where('bd.flditemname', 'Family Registration Mode(Family)') ->where(function ($query) { $query->whereNotIn('fldrelation', ['Son', 'Daughter']) ->orWhereNull('fldrelation'); }) ->groupBy(DB::raw('cast(bd.fldtime as date)')) ->select( DB::raw('cast(bd.fldtime as date) as fldtime'), DB::raw('SUM(CASE WHEN fldptsex = "Male" AND bd.fldbillno LIKE "CAF%" THEN 1 ELSE 0 END) as male_count'), DB::raw('SUM(CASE WHEN fldptsex = "Female" AND bd.fldbillno LIKE "CAF%" THEN 1 ELSE 0 END) as female_count'), DB::raw('SUM(CASE WHEN fldptsex = "Male" AND bd.fldbillno LIKE "CREF%" THEN 1 ELSE 0 END) as male_return_count'), DB::raw('SUM(CASE WHEN fldptsex = "Female" AND bd.fldbillno LIKE "CREF%" THEN 1 ELSE 0 END) as female_return_count'), DB::raw('SUM(CASE WHEN fldptsex = "Other" AND bd.fldbillno LIKE "CAF%" THEN 1 ELSE 0 END) as others_count'), DB::raw('SUM(CASE WHEN fldptsex = "Other" AND bd.fldbillno LIKE "CREF%" THEN 1 ELSE 0 END) as others_return_count') ) ->orderBy('bd.fldtime') ->get()

firstly to get a quick query with search conditions the first way is to create an index for each column used as search criteria. Did you create index on fldtime, fldptsex ,fdlrelation, fldbillno columns ? try and retrive query's delay execution. secondly avoid this part SUM(CASE WHEN .... and find a way to use COUNT with GROUP BY and HAVING clasuses.

And format the code.

I format the code for better discussion:

$results = DB::table('tblencounter') 
    ->join('tblpatientinfo', 'tblpatientinfo.fldpatientval', '=', 'tblencounter.fldpatientval') 
    ->join('tblpatbilling as bd', 'bd.fldencounterval', '=', 'tblencounter.fldencounterval') 
    ->whereRaw('cast(bd.fldtime as date) >= ?', [$fromdate])
    ->whereRaw('cast(bd.fldtime as date) <= ?', [$todate])
    ->whereIn('bd.fldcomp', ['comp57', 'comp51', 'comp52'])
    ->where('bd.flditemname', 'Family Registration Mode(Family)')
    ->where(function ($query) { 
        $query->whereNotIn('fldrelation', ['Son', 'Daughter'])
            ->orWhereNull('fldrelation'); 
    })
    ->groupBy(DB::raw('cast(bd.fldtime as date)'))
    ->select( 
        DB::raw('cast(bd.fldtime as date) as fldtime'), 
        DB::raw('SUM(CASE WHEN fldptsex = "Male" AND bd.fldbillno LIKE "CAF%" THEN 1 ELSE 0 END) as male_count'), 
        DB::raw('SUM(CASE WHEN fldptsex = "Female" AND bd.fldbillno LIKE "CAF%" THEN 1 ELSE 0 END) as female_count'), 
        DB::raw('SUM(CASE WHEN fldptsex = "Male" AND bd.fldbillno LIKE "CREF%" THEN 1 ELSE 0 END) as male_return_count'),
        DB::raw('SUM(CASE WHEN fldptsex = "Female" AND bd.fldbillno LIKE "CREF%" THEN 1 ELSE 0 END) as female_return_count'),
        DB::raw('SUM(CASE WHEN fldptsex = "Other" AND bd.fldbillno LIKE "CAF%" THEN 1 ELSE 0 END) as others_count'), 
        DB::raw('SUM(CASE WHEN fldptsex = "Other" AND bd.fldbillno LIKE "CREF%" THEN 1 ELSE 0 END) as others_return_count'))
    ->orderBy('bd.fldtime')
    ->get();

How often do you need the results? Is it alright to cache the results?

You can use the explain method to check what indices are being used:

DB::table('tblencounter')
    ->(...)
    ->explain();

Have a look at this course and this course, they might have something you need.

Thank you all for your kind response. I am extremely sorry for the format of the code. But I have got the solution, that using cast in fldtime has caused the issue. It would be kind if you guys could enlighten that why using cast caused the increase in execution time for the above code?