jeudi 3 février 2022

How can i optimize my query to deal with 10 thousand reocrds in MySql table from Laravel 5.5?

I have 10 thousand records in the MySQL table. I have used indexing to get a better result and all other techniques that are available in WWW. when records were around 3000 then it was taking 3 to 7 seconds but after that, my system takes 15 to 20 seconds to get data. that is too high. I want to reduce this. I am using Laravel 5.5.

I am showing the last 12 months revenue of bookings in Graph.

function pastTwelveMonthsTotalRevenue()
{
    $month = false;
    $today = Carbon\Carbon::now();

    $firstDay_this_month = new Carbon\Carbon('first day of this month');
    $start_date_this_month = $firstDay_this_month->startOfDay();
    $todayYear = $start_date_this_month->subYear(1)->startOfDay();
    $lastDay_last_month = new Carbon\Carbon('last day of last month');
    $end_date_last_month = $lastDay_last_month->endOfDay();



   // filtering all booking id with all condition.   
    $bookings = Booking::whereIn('trip_status', [1, 2])
        ->whereIn('trip_status', [1, 2])
        ->where('status', 1)
        ->where('booking_or_quotes', 1)
        ->whereBetween('booking.booking_time', [$todayYear, $end_date_last_month]);

    if ($franchisees) {
        $bookings->where('franchisees_id', $franchisees);
    }
    else
    {
        $bookings->whereHas('franchisees',function ($q){ $q->where('test_franchisee',0); });
    }

    $bookingsId =  $bookings->select('id')->get()->pluck('id');

    // now directly collecting those records that have filtered.
    // to get quickly i am only uisng filtered bookings Id here.

    $query = \App\BookingDetails::select('booking.booking_time',
        'booking.price_without_vat', 'booking.custom_price','booking_details.*',
        DB::raw("DATE_FORMAT(booking_time,'%Y%m')  as dt"))
        ->join('booking', 'booking.id', '=', 'booking_details.booking_id')
        ->whereIn( 'booking_id' , $bookingsId )
        ->whereBetween('booking.booking_time', [$todayYear, $end_date_last_month])
        ->orderBy(DB::raw("dt"));

    $query->with("booking:id,booking_time,price_without_vat,custom_price");
    $bookingModels = $query->get();

    $dataChart = array();
    $chartLabels = array();
    $color = array();
    $data = array();

    if (collect($bookingModels)->count()) {
        // Group all record on Y-M date order.
        $bookingModelCls = collect($bookingModels)->groupBy(DB::raw("dt"));

        foreach ($bookingModelCls as $key => $models) {

            $month = substr($key, 4, 2);

            $total_ern = $total_exp = 0;

            if (collect($models)->count()) {

                $month = $month * 1;

                $driverRevenue =  graphDriverTotalRevenue($models);
                $companionRevenue = graphCompanionTotalRevenue($models);
                $profit =  $driverRevenue + $companionRevenue ;
                $data[] = round($profit, 2);
                $color[] = getColor($key);
                $chartLabels[] = getMonthsName($month) . "'" . substr($models[0]['dt'], 2, 2);
            }
        }
    }


    $dataChart[] = array(
        'label' => false,
        'fill' => false,
        'backgroundColor' => $color,
        'borderColor' => $color,
        'data' => $data
    );

    return array(
        'labels' => $chartLabels,
        'data' => $dataChart
    );

}

Here in this method, I am using Inner-Join. using that I can get all data that is coming from the BookingDetails Table. but this code was written by a different user and due to lack of time, I did not change the whole code. so i used ->with("bookingDetails") to get data from Booking Details Tables.

So Data is coming around 4 to 6 seconds but when it is being loaded into Chat(Graph). It is taking 10 to 20 seconds or sometimes it is crashing the browser.

enter image description here



from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/gzwsf67qp
via IFTTT

Aucun commentaire:

Enregistrer un commentaire