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.

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