Laravel use mysql indexing

Question

I have this bad code (sorry) which using too many of MySQL queries sorry its too long

    return view('dashboard.homepage', array(
            'DriversNumberApproved'      => AppModelsDrivers::where('is_approved', 1)->count(),
            'DriversNumberUNApproved'      => AppModelsDrivers::where('is_approved', 0)->count(),
            'DriversOnline'      => AppModelsDrivers::where('is_active', 1)->count(),
            'DriversOnlineShow'      => AppModelsDrivers::where('is_active', 1)->paginate(5)->appends(request()->query()),
            'DriversOffline'      => AppModelsDrivers::where('is_active', 0)->where('is_approved', 1)->count(),
            'TodayMoneyTotal'      => AppModelsRequest::where('is_completed', 1)
                ->whereMonth('request_start_time', now()->month)
                ->whereDay('request_start_time', now()->day)
                ->whereYear('request_start_time', now()->year)->sum('total'),
            'TodayMoneyTotalP'      => AppModelsRequest::where('is_completed', 1)
                ->whereMonth('request_start_time', now()->month)
                ->whereDay('request_start_time', now()->day)
                ->whereYear('request_start_time', now()->year)->sum('total') / 5000 * 100,

            //This Week
            'TodayMoneyTotalWeek'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->sum('total'),
            'TodayMoneyTotalWeekP'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->sum('total') / 5000 * 100,


            //This Month
            'TodayMoneyTotalMonth'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfMonth(), Carbon::now()
                    ->endOfMonth()])->sum('total'),
            'TodayMoneyTotalMonthP'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfMonth(), Carbon::now()
                    ->endOfMonth()])->sum('total') / 5000 * 100,

//            Last 30 days
//            'TodayMoneyTotal30Last'      => AppModelsRequest::where('is_completed', 1)->whereDate('request_start_time', '=', Carbon::now()->subMonth()->month)->sum('total'),
//            'TodayMoneyTotal30LastP'      => AppModelsRequest::where('is_completed', 1)->whereDate('request_start_time', '=', Carbon::now()->subMonth()->month)->sum('total') / 5000 * 100,


            'TodayTripsTotal'      => AppModelsRequest::where('is_completed', 1)
                ->whereMonth('request_start_time', now()->month)
                ->whereDay('request_start_time', now()->day)
                ->whereYear('request_start_time', now()->year)->count(),
            'TodayTripsTotalP'      => AppModelsRequest::where('is_completed', 1)
                ->whereMonth('request_start_time', now()->month)
                ->whereDay('request_start_time', now()->day)
                ->whereYear('request_start_time', now()->year)->count() / 100 * 100,


            //This Week
            'TodayTripsTotalWeek'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->count(),
            'TodayTripsTotalWeekP'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->count() / 100 * 100,



            //This Month
            'TodayTripsTotalMonth'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfMonth(), Carbon::now()
                    ->endOfMonth()])->count(),
            'TodayTripsTotalMonthP'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfMonth(), Carbon::now()
                    ->endOfMonth()])->count() / 100 * 100,




            'TodayDistanceTotal'      => AppModelsRequest::where('is_completed', 1)
                ->whereMonth('request_start_time', now()->month)
                ->whereDay('request_start_time', now()->day)
                ->whereYear('request_start_time', now()->year)->sum('distance'),
            'TodayDistanceTotalP'      => AppModelsRequest::where('is_completed', 1)
                ->whereMonth('request_start_time', now()->month)
                ->whereDay('request_start_time', now()->day)
                ->whereYear('request_start_time', now()->year)->sum('distance') / 500 * 100,


            //This week
            'TodayDistanceWeekTotal'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->sum('distance'),
            'TodayDistanceWeekTotalP'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->sum('distance') / 500 * 100,





            //This Month
            'TodayDistanceMonthTotal'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfMonth(), Carbon::now()
                    ->endOfMonth()])->sum('distance'),
            'TodayDistanceMonthTotalP'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfMonth(), Carbon::now()
                    ->endOfMonth()])->sum('distance') / 500 * 100,






            'TodayTimeTotal'      => AppModelsRequest::where('is_completed', 1)
                ->whereMonth('request_start_time', now()->month)
                ->whereDay('request_start_time', now()->day)
                ->whereYear('request_start_time', now()->year)->sum('time'),
            'TodayTimeTotalP'      => AppModelsRequest::where('is_completed', 1)
                ->whereMonth('request_start_time', now()->month)
                ->whereDay('request_start_time', now()->day)
                ->whereYear('request_start_time', now()->year)->sum('time') / 5000 * 100,


            //This Week
            'TodayTimeWeekTotal'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->sum('time'),
            'TodayTimeWeekTotalP'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->sum('time') / 5000 * 100,




            //This Month
            'TodayTimeMonthTotal'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfMonth(), Carbon::now()
                    ->endOfMonth()])->sum('time'),
            'TodayTimeMonthTotalP'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfMonth(), Carbon::now()
                    ->endOfMonth()])->sum('time') / 5000 * 100,





            'TodayEarningsTotal'      => AppModelsRequest::where('is_completed', 1)
                ->whereMonth('request_start_time', now()->month)
                ->whereDay('request_start_time', now()->day)
                ->whereYear('request_start_time', now()->year)->sum('refund_remaining'),
            'TodayEarningsTotalP'      => AppModelsRequest::where('is_completed', 1)
                ->whereMonth('request_start_time', now()->month)
                ->whereDay('request_start_time', now()->day)
                ->whereYear('request_start_time', now()->year)->sum('refund_remaining') / 5000 * 100,


            //This Week
            'TodayEarningsWeekTotal'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->sum('refund_remaining'),
            'TodayEarningsWeekTotalP'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->sum('refund_remaining') / 5000 * 100,



            //This Month
            'TodayEarningsMonthTotal'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfMonth(), Carbon::now()
                    ->endOfMonth()])->sum('refund_remaining'),
            'TodayEarningsMonthTotalP'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfMonth(), Carbon::now()
                    ->endOfMonth()])->sum('refund_remaining') / 5000 * 100,




            //This Week
            'ThisWeekDriverRegistered'      => AppModelsDrivers::where('is_approved', 1)->whereBetween('created_at', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->count(),

            'ThisWeekTrips'      => AppModelsRequest::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->count(),

            'ThisWeekAutoCancelledTrips'      => AppModelsRequest::where('is_cancelled', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->count(),

            'ThisWeekDriverRegisteredMonday'      => AppModelsDrivers::where('is_approved', 1)
                ->whereDay('created_at', $monday)
                ->count() / 5000 * 100,
            'ThisWeekTripsMonday'      => AppModelsRequest::where('is_completed', 1)
                ->whereDay('request_start_time', $monday)->count()
                / 500 * 100,
            'ThisWeekAutoCancelledTripsMonday'      => AppModelsRequest::where('is_cancelled', 1)
                ->whereDay('request_start_time', $monday)->count()
                / 500 * 100,

            'ThisWeekDriverRegisteredTuesday'      => AppModelsDrivers::where('is_approved', 1)
                ->whereDay('created_at', $tuesday)
                ->count() / 5000 * 100,
            'ThisWeekTripsTuesday'      => AppModelsRequest::where('is_completed', 1)
                ->whereDay('request_start_time', $tuesday)->count()
                / 500 * 100,
            'ThisWeekAutoCancelledTripsTuesday'      => AppModelsRequest::where('is_cancelled', 1)
                ->whereDay('request_start_time', $tuesday)->count()
                / 500 * 100,


            'ThisWeekDriverRegisteredWednesday'      => AppModelsDrivers::where('is_approved', 1)
                ->whereDay('created_at', $wednesday)
                ->count() / 5000 * 100,
            'ThisWeekTripsWednesday'      => AppModelsRequest::where('is_completed', 1)
                ->whereDay('request_start_time', $wednesday)->count()
                / 500 * 100,
            'ThisWeekAutoCancelledTr

Answer 1

Alright!! Let's do it.

First of all, I highly recommend using barryvdh/laravel-debugbar (GitHub). This will tell you exactly how many queries were fired and how much time each one took.

Now, let's talk about optimisation.

• Use select() whenever possible. If a table has 20 columns and about 1000 rows and all your are doing is count() or sum() then fetching all the data doesn't make sense.
• Your AppModelsDrivers is being used multiple times. Here's what I recommend:
  • Query 1 -> AppModelsDrivers::where('is_approved', 1)->count();
  • Query 2 -> AppModelsDrivers::where('is_approved', 0)->count();
  • Solution -> $drivers = AppModelsDrivers::whereIn('is_approved', [0, 1])->get();
    Here you can take adcantage of Laravel Collection
    $drivers->where('is_approved', 1)->count()
$drivers->where('is_approved', 0)->count()
    Before your query was running twice and now with collect() only once.
• Indexing is another solution. Check out this stack overflow discussion.
• Use caching if you can. (docs)
• Another thing to consider is how you are displaying this data. As you said the page is taking too long. Perhaps, you are using nested loops. (Can't say much since I don't see your blade file).
• Another thing that many developers miss is data type missmatch. If your is_approved column in sql is not an integer then while querying with where(string, int) will result in load time. It's better to have same data type i.e. where(int, int)

Hope this helps. Cheers!