0

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

    return view('dashboard.homepage', array(
            'DriversNumberApproved'      => \App\Models\Drivers::where('is_approved', 1)->count(),
            'DriversNumberUNApproved'      => \App\Models\Drivers::where('is_approved', 0)->count(),
            'DriversOnline'      => \App\Models\Drivers::where('is_active', 1)->count(),
            'DriversOnlineShow'      => \App\Models\Drivers::where('is_active', 1)->paginate(5)->appends(request()->query()),
            'DriversOffline'      => \App\Models\Drivers::where('is_active', 0)->where('is_approved', 1)->count(),
            'TodayMoneyTotal'      => \App\Models\Request::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'      => \App\Models\Request::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'      => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->sum('total'),
            'TodayMoneyTotalWeekP'      => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->sum('total') / 5000 * 100,


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

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


            'TodayTripsTotal'      => \App\Models\Request::where('is_completed', 1)
                ->whereMonth('request_start_time', now()->month)
                ->whereDay('request_start_time', now()->day)
                ->whereYear('request_start_time', now()->year)->count(),
            'TodayTripsTotalP'      => \App\Models\Request::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'      => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->count(),
            'TodayTripsTotalWeekP'      => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->count() / 100 * 100,



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




            'TodayDistanceTotal'      => \App\Models\Request::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'      => \App\Models\Request::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'      => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->sum('distance'),
            'TodayDistanceWeekTotalP'      => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->sum('distance') / 500 * 100,





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






            'TodayTimeTotal'      => \App\Models\Request::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'      => \App\Models\Request::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'      => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->sum('time'),
            'TodayTimeWeekTotalP'      => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->sum('time') / 5000 * 100,




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





            'TodayEarningsTotal'      => \App\Models\Request::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'      => \App\Models\Request::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'      => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->sum('refund_remaining'),
            'TodayEarningsWeekTotalP'      => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time', 
                    [Carbon::now()->startOfWeek(), Carbon::now()])->sum('refund_remaining') / 5000 * 100,



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




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

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

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

            'ThisWeekDriverRegisteredMonday'      => \App\Models\Drivers::where('is_approved', 1)
                ->whereDay('created_at', $monday)
                ->count() / 5000 * 100,
            'ThisWeekTripsMonday'      => \App\Models\Request::where('is_completed', 1)
                ->whereDay('request_start_time', $monday)->count()
                / 500 * 100,
            'ThisWeekAutoCancelledTripsMonday'      => \App\Models\Request::where('is_cancelled', 1)
                ->whereDay('request_start_time', $monday)->count()
                / 500 * 100,

            'ThisWeekDriverRegisteredTuesday'      => \App\Models\Drivers::where('is_approved', 1)
                ->whereDay('created_at', $tuesday)
                ->count() / 5000 * 100,
            'ThisWeekTripsTuesday'      => \App\Models\Request::where('is_completed', 1)
                ->whereDay('request_start_time', $tuesday)->count()
                / 500 * 100,
            'ThisWeekAutoCancelledTripsTuesday'      => \App\Models\Request::where('is_cancelled', 1)
                ->whereDay('request_start_time', $tuesday)->count()
                / 500 * 100,


            'ThisWeekDriverRegisteredWednesday'      => \App\Models\Drivers::where('is_approved', 1)
                ->whereDay('created_at', $wednesday)
                ->count() / 5000 * 100,
            'ThisWeekTripsWednesday'      => \App\Models\Request::where('is_completed', 1)
                ->whereDay('request_start_time', $wednesday)->count()
                / 500 * 100,
            'ThisWeekAutoCancelledTripsWednesday'      => \App\Models\Request::where('is_cancelled', 1)
                ->whereDay('request_start_time', $wednesday)->count()
                / 500 * 100,

            'ThisWeekDriverRegisteredThursday'      => \App\Models\Drivers::where('is_approved', 1)
                ->whereDay('created_at', $thursday)
                ->count() / 5000 * 100,
            'ThisWeekTripsThursday'      => \App\Models\Request::where('is_completed', 1)
                ->whereDay('request_start_time', $thursday)->count()
                / 500 * 100,
            'ThisWeekAutoCancelledTripsThursday'      => \App\Models\Request::where('is_cancelled', 1)
                ->whereDay('request_start_time', $thursday)->count()
                / 500 * 100,

            'ThisWeekDriverRegisteredFriday'      => \App\Models\Drivers::where('is_approved', 1)
                ->whereDay('created_at', $friday)
                ->count() / 5000 * 100,
            'ThisWeekTripsFriday'      => \App\Models\Request::where('is_completed', 1)
                ->whereDay('request_start_time', $friday)->count()
                / 500 * 100,
            'ThisWeekAutoCancelledTripsFriday'      => \App\Models\Request::where('is_cancelled', 1)
                ->whereDay('request_start_time', $friday)->count()
                / 500 * 100,

            'ThisWeekDriverRegisteredSaturday'      => \App\Models\Drivers::where('is_approved', 1)
                ->whereDay('created_at', $saturday)
                ->count() / 5000 * 100,
            'ThisWeekTripsSaturday'      => \App\Models\Request::where('is_completed', 1)
                ->whereDay('request_start_time', $saturday)->count()
                / 500 * 100,
            'ThisWeekAutoCancelledTripsSaturday'      => \App\Models\Request::where('is_cancelled', 1)
                ->whereDay('request_start_time', $saturday)->count()
                / 500 * 100,

            'ThisWeekDriverRegisteredSunday'      => \App\Models\Drivers::where('is_approved', 1)
                ->whereDay('created_at', $sunday)
                ->count() / 5000 * 100,
            'ThisWeekTripsSunday'      => \App\Models\Request::where('is_completed', 1)
                ->whereDay('request_start_time', $sunday)->count()
                / 500 * 100,
            'ThisWeekAutoCancelledTripsSunday'      => \App\Models\Request::where('is_cancelled', 1)
                ->whereDay('request_start_time', $sunday)->count()
                / 500 * 100,

             'TotalBlockedDrivers'      => \App\Models\Drivers::where('block', 1)->count(),
            'role'          => 'admin',
        ));

Cuz of the queries on MySQL page take forever to load I'm thinking to use indexing but I'm using ->Where too much here and too many specific things any help? I'm not really sure how to optimize it cuz it takes so many to load the page

life isnothingtome
  • 87
  • 1
  • 8

1 Answers1

3

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 \App\Models\Drivers is being used multiple times. Here's what I recommend:
    • Query 1 -> \App\Models\Drivers::where('is_approved', 1)->count();
    • Query 2 -> \App\Models\Drivers::where('is_approved', 0)->count();
    • Solution -> $drivers = \App\Models\Drivers::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!

Digvijay
  • 7,836
  • 3
  • 32
  • 53