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

September 30, 202308: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

Event	Amount
Number of Queries	2882
Model Hydration	10000
Memory Uses	13 MB
Processing Time	~11 Seconds

Table of Content

Code Investigation
⚠️ My Assessment
Before vs After
Full Code
Video Tutorial (in Bangla Language)

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.

Event	Before	After
Nuber of Queries:	2882	2
Nuber of Model Hydration:	10000	170
Total Memory Uses:	13 MB	2MB
Query Processing Time:	~11 Seconds	231 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.

laravel laravel-refactoring laravel-database laravel-optimization laravel-query

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

Summary

Table of Content

Code Investigation

⚠️ My Assessment

Functional Similarities

1. Targeting the User model

2. Ordering by created_at in descending order

3. Date range filtering

4. Filtering by gender:

5. Filtering by creator (createdby):

Proposed Optimization

Functional Similarities

1. Date Filtering

2. JOIN operation

3. Filtering by Country

4. Filtering by Gender

5. Filtering by Account Creator (createdby)

6. User’s Role Filtering

7. Filtering by Complete Profiles

Proposed Optimization

Before vs After

Full Code

Video Tutorial (in Bangla Language)

1. Targeting the `User` model

2. Ordering by `created_at` in descending order

4. Filtering by `gender`:

5. Filtering by creator (`createdby`):