Optimize Laravel database Query from 2882 to 2 queries only | Laravel Refactoring

Optimize Laravel database Query from 2882 to 2 queries only | Laravel Refactoring

Posted on:September 30, 2023 at 08:00 PM

Hello everyone 👋

Building laravel dashboard will be one of the difficult tasks. The most challenging job for the dashboard is maintaining low amount of database queries, memory uses and processing time. Today, I will show you how to optimize an existing code that has 2882 number of queries for counting some stats and I will achieve the same result with the 2 queries only.

I found a code that a following output. I just install the debagbar package that help use to count the total number of queries, memory uses and processing time.

Summary

EventAmount
Number of Queries2882
Model Hydration10000
Memory Uses13 MB
Processing Time~11 Seconds

Table of Content

Code Investigation

Let’s dig into the code.

ℹ️ It’s a huge codebase. Therefore I want to make it collapse.

public function showUsersStatistics(Request $request)
{
        $from = $request->input('from');
        if ($from == '00-00-00' or $from == '') {
            $from = '2015-01-01 00:00:00';
        }

        $to = $request->input('to');
        if ($to == '00-00-00' or $to == '') {
            $to = Carbon::now('Singapore');
        }

        $fromdate = Carbon::parse($from)->addHour(-8);
        $todate = Carbon::parse($to)->addHour(-8);

        $users = '';

        $countusers = User::where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->orderBy('created_at', 'desc')->count();

        $countrylist = ["Afghanistan", "Albania", "Algeria", "Andorra", "Angola", "Anguilla", "Argentina", "Armenia", "Australia", "Austria", "Azerbaijan", "Bahamas", "Bahrain", "Bangladesh", "Barbados", "Belarus", "Belgium", "Bermuda", "Bhutan", "Bolivia", "Bosnia", "Botswana", "Brazil", "British Virgin Islands", "Brunei", "Bulgaria", "Burkina Faso", "Cambodia", "Cameroon", "Chad", "Chile", "China", "Colombia", "Congo", "Costa Rica", "Cote D Ivoire", "Croatia", "Cuba", "Cyprus", "Czech Republic", "Denmark", "Djibouti", "Dominica", "Dominican Republic", "Ecuador", "Egypt", "El Salvador", "Equatorial Guinea", "Estonia", "Ethiopia", "Fiji", "Finland", "France", "Gambia", "Georgia", "Germany", "Ghana", "Greece", "Greenland", "Guatemala", "Guinea", "Haiti", "Honduras", "Hong Kong", "Hungary", "Iceland", "India", "Indonesia", "Iran", "Iraq", "Ireland", "Israel", "Italy", "Jamaica", "Japan", "Jersey", "Jordan", "Kazakhstan", "Kenya", "Kuwait", "Kyrgyz Republic", "Laos", "Latvia", "Lebanon", "Lesotho", "Liberia", "Libya", "Liechtenstein", "Lithuania", "Luxembourg", "Macau", "Macedonia", "Madagascar", "Malawi", "Malaysia", "Maldives", "Mali", "Malta", "Mauritania", "Mauritius", "Mexico", "Moldova", "Monaco", "Mongolia", "Morocco", "Mozambique", "Namibia", "Nepal", "Netherlands", "New Zealand", "Nicaragua", "Niger", "Nigeria", "Norway", "Oman", "Pakistan", "Palestine", "Panama", "Papua New Guinea", "Paraguay", "Peru", "Philippines", "Poland", "Portugal", "Puerto Rico", "Qatar", "Romania", "Russia", "Rwanda", "Samoa", "Saudi Arabia", "Senegal", "Serbia", "Sierra Leone", "Singapore", "Slovakia", "Slovenia", "South Africa", "South Korea", "Spain", "Sri Lanka", "Sudan", "Swaziland", "Sweden", "Switzerland", "Syria", "Taiwan", "Tajikistan", "Tanzania", "Thailand", "Togo", "Tonga", "Trinidad &amp; Tobago", "Tunisia", "Turkey", "Turkmenistan", "Uganda", "Ukraine", "United States", "United Arab Emirates", "United Kingdom", "Uruguay", "Uzbekistan", "Venezuela", "Vietnam", "Virgin Islands (US)", "Yemen", "Zambia", "Zimbabwe"];

        $countries = array();

        foreach ($countrylist as $country) {
            $users = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
            /////////By User
            //Male
            $usersmale = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'man' AND us.createdby != 'admin' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
            $usersmaleSD = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'man' AND us.createdby != 'admin' AND us.wanttobe = 'sugardaddy' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
            $usersmaleSB = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'man' AND us.createdby != 'admin' AND us.wanttobe = 'sugarbaby' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
            // Male Complete profiles
            $usersmaleSDcomplete = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'man' AND us.createdby != 'admin' AND us.wanttobe = 'sugardaddy' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' AND us.aboutme !='' AND us.lookingfordetails !='' AND us.tagline !='' AND us.username !='' AND us.profilephoto !='' ");
            $usersmaleSBcomplete = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'man' AND us.createdby != 'admin' AND us.wanttobe = 'sugarbaby' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' AND us.aboutme !='' AND us.lookingfordetails !='' AND us.tagline !='' AND us.username !='' AND us.profilephoto !='' ");

            //Female
            $usersfemale = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'woman' AND us.createdby != 'admin' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
            $usersfemaleSD = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'woman' AND us.createdby != 'admin' AND us.wanttobe = 'sugardaddy' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
            $usersfemaleSB = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'woman' AND us.createdby != 'admin' AND us.wanttobe = 'sugarbaby' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
            //Female Complete Profiles
            $usersfemaleSDcomplete = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'woman' AND us.createdby != 'admin' AND us.wanttobe = 'sugardaddy' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' AND us.aboutme !='' AND us.lookingfordetails !='' AND us.tagline !='' AND us.username !='' AND us.profilephoto !=''  ");
            $usersfemaleSBcomplete = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'woman' AND us.createdby != 'admin' AND us.wanttobe = 'sugarbaby' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' AND us.aboutme !='' AND us.lookingfordetails !='' AND us.tagline !='' AND us.username !='' AND us.profilephoto !='' ");

            //Created By admin Needs Fixes
            $adminmale = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'man' AND us.createdby = 'admin' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
            $adminmaleSD = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'man' AND us.createdby = 'admin' AND us.wanttobe = 'sugardaddy' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
            $adminmaleSB = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'man' AND us.createdby = 'admin' AND us.wanttobe = 'sugarbaby' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");

            $adminfemale = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'woman' AND us.createdby = 'admin' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
            $adminfemaleSD = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'woman' AND us.createdby = 'admin' AND us.wanttobe = 'sugardaddy' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
            $adminfemaleSB = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'woman' AND us.createdby = 'admin' AND us.wanttobe = 'sugarbaby' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");

            if (empty($users)) {
                continue;
            }

            $countryf = array([
                'country' => $country,
                'total' => count($users),
                'usersmale' => count($usersmale),
                'usersmaleSD' => count($usersmaleSD),
                'usersmaleSB' => count($usersmaleSB),
                'usersmaleSDcomplete' => count($usersmaleSDcomplete),
                'usersmaleSBcomplete' => count($usersmaleSBcomplete),
                'usersfemale' => count($usersfemale),
                'usersfemaleSD' => count($usersfemaleSD),
                'usersfemaleSB' => count($usersfemaleSB),
                'usersfemaleSDcomplete' => count($usersfemaleSDcomplete),
                'usersfemaleSBcomplete' => count($usersfemaleSBcomplete),
                'adminmale' => count($adminmale),
                'adminmaleSD' => count($adminmaleSD),
                'adminmaleSB' => count($adminmaleSB),
                'adminfemale' => count($adminfemale),
                'adminfemaleSD' => count($adminfemaleSD),
                'adminfemaleSB' => count($adminfemaleSB),
            ]);

            $countries = array_merge($countries, $countryf);
        }

        //Sort Countries by total in DESC format
        usort($countries, function ($a, $b) {
            return $b['total'] - $a['total'];
        });

        $totalman = User::orderby('created_at', 'desc')->where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->where('gender', '=', 'man')->count();
        $totalwoman = User::orderby('created_at', 'desc')->where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->where('gender', '!=', 'man')->count();
        //counts accounts created by admin
        $adminAccounts = User::orderBy('created_at', 'desc')->where('createdby', 'admin')->where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->count();
        //counts accounts created by themself
        $selfAccounts = User::orderBy('created_at', 'desc')->where('createdby', '!=', 'admin')->where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->count();
        $manAccountsAdmin = User::orderby('created_at', 'desc')->where('gender', '=', 'man')->where('createdby', '=', 'admin')->where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->count();
        $womanAccountsAdmin = User::orderby('created_at', 'desc')->where('gender', '=', 'woman')->where('createdby', '=', 'admin')->where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->count();
        $manAccountsUser = User::orderby('created_at', 'desc')->where('gender', '=', 'man')->where('createdby', '!=', 'admin')->where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->count();
        $womanAccountsUser = User::orderby('created_at', 'desc')->where('gender', '=', 'woman')->where('createdby', '!=', 'admin')->where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->count();

        $referrals = array();

        return view('show-users-statistics')
            ->with([
                'users' => $users,
                'adminaccounts' => $adminAccounts,
                'selfaccounts' => $selfAccounts,
                'manaccountsadmin' => $manAccountsAdmin,
                'womanaccountsadmin' => $womanAccountsAdmin,
                'manaccountsuser' => $manAccountsUser,
                'womanaccountsuser' => $womanAccountsUser,
                'totalman' => $totalman,
                'totalwoman' => $totalwoman,
                'countusers' => $countusers,
                'countries' => $countries,
                'referrals' => $referrals,
            ]);
}

⚠️ My Assessment

(No blame) It’s not an ideal way to write code because the number of queries, memoery uses, and processing time.

$totalman = User::orderby('created_at', 'desc')->where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->where('gender', '=', 'man')->count();
$totalwoman = User::orderby('created_at', 'desc')->where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->where('gender', '!=', 'man')->count();
//counts accounts created by admin
$adminAccounts = User::orderBy('created_at', 'desc')->where('createdby', 'admin')->where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->count();
//counts accounts created by themself
$selfAccounts = User::orderBy('created_at', 'desc')->where('createdby', '!=', 'admin')->where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->count();
$manAccountsAdmin = User::orderby('created_at', 'desc')->where('gender', '=', 'man')->where('createdby', '=', 'admin')->where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->count();
$womanAccountsAdmin = User::orderby('created_at', 'desc')->where('gender', '=', 'woman')->where('createdby', '=', 'admin')->where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->count();
$manAccountsUser = User::orderby('created_at', 'desc')->where('gender', '=', 'man')->where('createdby', '!=', 'admin')->where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->count();
$womanAccountsUser = User::orderby('created_at', 'desc')->where('gender', '=', 'woman')->where('createdby', '!=', 'admin')->where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->count();

Functional Similarities

I realised that, the above code has some similarities. Here is my assessment:

1. Targeting the User model

  • Each line is querying the User model to retrieve some count based on certain conditions.

2. Ordering by created_at in descending order

  • Each query uses orderBy('created_at', 'desc') to order the results by the creation date in descending order.

3. Date range filtering

  • Every query filters users based on a date range:
    where('created_at', '>=', $fromdate)
    where('created_at', '<=', $todate)

4. Filtering by gender:

  • Several queries filter users by their gender:
    where('gender', '=', 'man') or where('gender', '=', 'woman')

5. Filtering by creator (createdby):

  • Multiple queries distinguish users based on who created the account:
    where('createdby', 'admin') or where('createdby', '!=', 'admin')

Proposed Optimization

So, I propose to use Mysql CASE in laravel query for 8 queries, which will redecue the 7 query by acheiving it 1 only. Here is my suggestion:

$stats = User::query()
          ->whereBetween('created_at', [$fromdate, $todate])
          ->select(DB::raw("
              count(id) as total,
              SUM(CASE WHEN gender = 'man' THEN 1 ELSE 0 END) as totalman,
              SUM(CASE WHEN gender != 'man' THEN 1 ELSE 0 END) as totalwoman,
              SUM(CASE WHEN createdby = 'admin' THEN 1 ELSE 0 END) as adminAccounts,
              SUM(CASE WHEN createdby != 'admin' THEN 1 ELSE 0 END) as selfAccounts,
              SUM(CASE WHEN gender = 'man' AND createdby = 'admin' THEN 1 ELSE 0 END) as manAccountsAdmin,
              SUM(CASE WHEN gender = 'woman' AND createdby = 'admin' THEN 1 ELSE 0 END) as womanAccountsAdmin,
              SUM(CASE WHEN gender = 'man' AND createdby != 'admin' THEN 1 ELSE 0 END) as manAccountsUser,
              SUM(CASE WHEN gender = 'woman' AND createdby != 'admin' THEN 1 ELSE 0 END) as womanAccountsUser
        "))->first();

✅ It literally providing the same result for us by only 1 query instead of 8.

$countusers = User::where('created_at', '>=', $fromdate)->where('created_at', '<=', $todate)->orderBy('created_at', 'desc')->count();

$countrylist = ["Afghanistan", "Albania", "Algeria", "Andorra", "Angola", "Anguilla", "Argentina", "Armenia", "Australia", "Austria", "Azerbaijan", "Bahamas", "Bahrain", "Bangladesh", "Barbados", "Belarus", "Belgium", "Bermuda", "Bhutan", "Bolivia", "Bosnia", "Botswana", "Brazil", "British Virgin Islands", "Brunei", "Bulgaria", "Burkina Faso", "Cambodia", "Cameroon", "Chad", "Chile", "China", "Colombia", "Congo", "Costa Rica", "Cote D Ivoire", "Croatia", "Cuba", "Cyprus", "Czech Republic", "Denmark", "Djibouti", "Dominica", "Dominican Republic", "Ecuador", "Egypt", "El Salvador", "Equatorial Guinea", "Estonia", "Ethiopia", "Fiji", "Finland", "France", "Gambia", "Georgia", "Germany", "Ghana", "Greece", "Greenland", "Guatemala", "Guinea", "Haiti", "Honduras", "Hong Kong", "Hungary", "Iceland", "India", "Indonesia", "Iran", "Iraq", "Ireland", "Israel", "Italy", "Jamaica", "Japan", "Jersey", "Jordan", "Kazakhstan", "Kenya", "Kuwait", "Kyrgyz Republic", "Laos", "Latvia", "Lebanon", "Lesotho", "Liberia", "Libya", "Liechtenstein", "Lithuania", "Luxembourg", "Macau", "Macedonia", "Madagascar", "Malawi", "Malaysia", "Maldives", "Mali", "Malta", "Mauritania", "Mauritius", "Mexico", "Moldova", "Monaco", "Mongolia", "Morocco", "Mozambique", "Namibia", "Nepal", "Netherlands", "New Zealand", "Nicaragua", "Niger", "Nigeria", "Norway", "Oman", "Pakistan", "Palestine", "Panama", "Papua New Guinea", "Paraguay", "Peru", "Philippines", "Poland", "Portugal", "Puerto Rico", "Qatar", "Romania", "Russia", "Rwanda", "Samoa", "Saudi Arabia", "Senegal", "Serbia", "Sierra Leone", "Singapore", "Slovakia", "Slovenia", "South Africa", "South Korea", "Spain", "Sri Lanka", "Sudan", "Swaziland", "Sweden", "Switzerland", "Syria", "Taiwan", "Tajikistan", "Tanzania", "Thailand", "Togo", "Tonga", "Trinidad &amp; Tobago", "Tunisia", "Turkey", "Turkmenistan", "Uganda", "Ukraine", "United States", "United Arab Emirates", "United Kingdom", "Uruguay", "Uzbekistan", "Venezuela", "Vietnam", "Virgin Islands (US)", "Yemen", "Zambia", "Zimbabwe"];

$countries = array();

foreach ($countrylist as $country) {
    $users = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
    /////////By User
    //Male
    $usersmale = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'man' AND us.createdby != 'admin' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
    $usersmaleSD = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'man' AND us.createdby != 'admin' AND us.wanttobe = 'sugardaddy' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
    $usersmaleSB = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'man' AND us.createdby != 'admin' AND us.wanttobe = 'sugarbaby' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
    // Male Complete profiles
    $usersmaleSDcomplete = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'man' AND us.createdby != 'admin' AND us.wanttobe = 'sugardaddy' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' AND us.aboutme !='' AND us.lookingfordetails !='' AND us.tagline !='' AND us.username !='' AND us.profilephoto !='' ");
    $usersmaleSBcomplete = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'man' AND us.createdby != 'admin' AND us.wanttobe = 'sugarbaby' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' AND us.aboutme !='' AND us.lookingfordetails !='' AND us.tagline !='' AND us.username !='' AND us.profilephoto !='' ");

    //Female
    $usersfemale = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'woman' AND us.createdby != 'admin' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
    $usersfemaleSD = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'woman' AND us.createdby != 'admin' AND us.wanttobe = 'sugardaddy' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
    $usersfemaleSB = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'woman' AND us.createdby != 'admin' AND us.wanttobe = 'sugarbaby' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
    //Female Complete Profiles
    $usersfemaleSDcomplete = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'woman' AND us.createdby != 'admin' AND us.wanttobe = 'sugardaddy' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' AND us.aboutme !='' AND us.lookingfordetails !='' AND us.tagline !='' AND us.username !='' AND us.profilephoto !=''  ");
    $usersfemaleSBcomplete = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'woman' AND us.createdby != 'admin' AND us.wanttobe = 'sugarbaby' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' AND us.aboutme !='' AND us.lookingfordetails !='' AND us.tagline !='' AND us.username !='' AND us.profilephoto !='' ");

    //Created By admin Needs Fixes
    $adminmale = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'man' AND us.createdby = 'admin' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
    $adminmaleSD = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'man' AND us.createdby = 'admin' AND us.wanttobe = 'sugardaddy' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
    $adminmaleSB = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'man' AND us.createdby = 'admin' AND us.wanttobe = 'sugarbaby' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");

    $adminfemale = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'woman' AND us.createdby = 'admin' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
    $adminfemaleSD = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'woman' AND us.createdby = 'admin' AND us.wanttobe = 'sugardaddy' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");
    $adminfemaleSB = DB::select("SELECT us.id FROM usercities as cities LEFT JOIN users as us on cities.userid = us.id Where cities.currentcountry LIKE '%" . $country . "%' AND us.gender = 'woman' AND us.createdby = 'admin' AND us.wanttobe = 'sugarbaby' AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "' ");

    if (empty($users)) {
        continue;
    }

    $countryf = array([
        'country' => $country,
        'total' => count($users),
        'usersmale' => count($usersmale),
        'usersmaleSD' => count($usersmaleSD),
        'usersmaleSB' => count($usersmaleSB),
        'usersmaleSDcomplete' => count($usersmaleSDcomplete),
        'usersmaleSBcomplete' => count($usersmaleSBcomplete),
        'usersfemale' => count($usersfemale),
        'usersfemaleSD' => count($usersfemaleSD),
        'usersfemaleSB' => count($usersfemaleSB),
        'usersfemaleSDcomplete' => count($usersfemaleSDcomplete),
        'usersfemaleSBcomplete' => count($usersfemaleSBcomplete),
        'adminmale' => count($adminmale),
        'adminmaleSD' => count($adminmaleSD),
        'adminmaleSB' => count($adminmaleSB),
        'adminfemale' => count($adminfemale),
        'adminfemaleSD' => count($adminfemaleSD),
        'adminfemaleSB' => count($adminfemaleSB),
    ]);

    $countries = array_merge($countries, $countryf);
}

//Sort Countries by total in DESC format
usort($countries, function ($a, $b) {
    return $b['total'] - $a['total'];
});

Functional Similarities

Here I can found following similarities:

1. Date Filtering

Each database query has a consistent filtering on the created_at column using a date range:

AND us.created_at BETWEEN '" . $from . "' AND '" . $to . "'

2. JOIN operation

All the user-based queries are performing a LEFT JOIN between usercities and users tables on userid:

LEFT JOIN users as us on cities.userid = us.id

3. Filtering by Country

For each country in the $countrylist, the queries filter users based on their current country:

cities.currentcountry LIKE '%" . $country . "%'

4. Filtering by Gender

Database queries distinguish between male (man) and female (woman) users. For instance:

AND us.gender = 'man'
AND us.gender = 'woman'

5. Filtering by Account Creator (createdby)

The database queries differentiate users based on who created the account:

AND us.createdby != 'admin'
AND us.createdby = 'admin'

6. User’s Role Filtering

There’s a distinction in queries based on what the user wants to be, i.e., sugardaddy or sugarbaby:

AND us.wanttobe = 'sugardaddy'
AND us.wanttobe = 'sugarbaby'

7. Filtering by Complete Profiles

Queries also filter users based on the completion of their profiles using multiple conditions like:

AND us.aboutme !='' AND us.lookingfordetails !='' AND us.tagline !='' AND us.username !='' AND us.profilephoto !=''

Proposed Optimization

I will us the CASE again to optimizat this code.

User::join('usercities', 'users.id', '=', 'usercities.userid')
    ->select(DB::raw("
        usercities.currentcountry,
        count(users.id) as total,

        SUM(CASE WHEN gender = 'man' AND createdby != 'admin' THEN 1 ELSE 0 END) as usersmale,
        SUM(CASE WHEN gender = 'man' AND createdby != 'admin' AND wanttobe = 'sugardaddy' THEN 1 ELSE 0 END) as usersmaleSD,
        SUM(CASE WHEN gender = 'man' AND createdby != 'admin' AND wanttobe = 'sugarbaby' THEN 1 ELSE 0 END) as usersmaleSB,
        SUM(CASE WHEN gender = 'man' AND createdby != 'admin' AND wanttobe = 'sugardaddy' AND aboutme != '' AND lookingfordetails != '' AND tagline != '' AND username != '' AND profilephoto != '' THEN 1 ELSE 0 END) as usersmaleSDcomplete,
        SUM(CASE WHEN gender = 'man' AND createdby != 'admin' AND wanttobe = 'sugarbaby' AND aboutme != '' AND lookingfordetails != '' AND tagline != '' AND username != '' AND profilephoto != '' THEN 1 ELSE 0 END) as usersmaleSBcomplete,

        SUM(CASE WHEN gender = 'woman' AND createdby != 'admin' THEN 1 ELSE 0 END) as usersfemale,
        SUM(CASE WHEN gender = 'woman' AND createdby != 'admin' AND wanttobe = 'sugardaddy' THEN 1 ELSE 0 END) as usersfemaleSD,
        SUM(CASE WHEN gender = 'woman' AND createdby != 'admin' AND wanttobe = 'sugarbaby' THEN 1 ELSE 0 END) as usersfemaleSB,
        SUM(CASE WHEN gender = 'woman' AND createdby != 'admin' AND wanttobe = 'sugardaddy' AND aboutme != '' AND lookingfordetails != '' AND tagline != '' AND username != '' AND profilephoto != '' THEN 1 ELSE 0 END) as usersfemaleSDcomplete,
        SUM(CASE WHEN gender = 'woman' AND createdby != 'admin' AND wanttobe = 'sugarbaby' AND aboutme != '' AND lookingfordetails != '' AND tagline != '' AND username != '' AND profilephoto != '' THEN 1 ELSE 0 END) as usersfemaleSBcomplete,

        SUM(CASE WHEN gender = 'man' AND createdby = 'admin' THEN 1 ELSE 0 END) as adminmale,
        SUM(CASE WHEN gender = 'man' AND createdby = 'admin' AND wanttobe = 'sugardaddy' THEN 1 ELSE 0 END) as adminmaleSD,
        SUM(CASE WHEN gender = 'man' AND createdby = 'admin' AND wanttobe = 'sugarbaby' THEN 1 ELSE 0 END) as adminmaleSB,
        SUM(CASE WHEN gender = 'woman' AND createdby = 'admin' THEN 1 ELSE 0 END) as adminfemale,
        SUM(CASE WHEN gender = 'woman' AND createdby = 'admin' AND wanttobe = 'sugardaddy' THEN 1 ELSE 0 END) as adminfemaleSD,
        SUM(CASE WHEN gender = 'woman' AND createdby = 'admin' AND wanttobe = 'sugarbaby' THEN 1 ELSE 0 END) as adminfemaleSB
    "))
    ->whereBetween('users.created_at', [$fromdate, $todate])
    ->groupBy('usercities.currentcountry')
    ->orderBy('total', 'desc')
    ->get();

✅ Huh, it redece from 2875 to 1 queries and provide use the same result.

This are the major changes that I can optimize from the given code. Here is the output:

Before vs After

Here is my before vs after comparism.

EventBeforeAfter
Nuber of Queries:28822
Nuber of Model Hydration:10000170
Total Memory Uses:13 MB2MB
Query Processing Time:~11 Seconds231 milliseconds

Full Code

I created a pull request in the github. You can find the the full code here.

Here is my proposed solution

Video Tutorial (in Bangla Language)

I have a tutorial for that optimization in Bangla language. If you are interested (ofcourse you need to be familiar with Bangla language), feel free to take a look.

I hope this solution will help you to solve some calculation in the future.

Thanks for reading.