I recently started using phpdebugbar start_measure(name, label) and stop_measure(name) to see how I can make performance improvements to my platform.

If you didn't know, you can use phpdebugbar to measure how long a certain function takes to execute. To find this out you simply add start_measure(name, label) at the beginning of your function and stop_measure(name) at the end of your function and when you run that snippet of code in your browser you will get a result in the timeline tab, like below:

As you can see from the image above, this snippet of code is taking over 2 seconds to execute and it's using 203MB of memory 😳. This can be improved, a lot!

Using Eloquent

Here is the snippet of code that is causing the issue:

$results = $request->user()->incomes()->get();

$years = $results->unique(function($item){
    return $item['date_income_received']->year;
})->map(function($item){
    return $item['date_income_received']->year;
})->sort()->toArray();

return $years;

The goal of this code is to get a collection of incomes in the database and iterate through them to get the unique years for the incomes. So for instance, if I have 100 incomes in the database and the incomes are for both 2020 and 2021, the results will be an array with ['2020', '2021']. This way I can display the years to the user and they can filter through the incomes based on those years.

What's the issue?

Well, the issue is with $results = $request->user()->incomes()->get();

The SQL statement being generated is as follow:

select * from `incomes` where `incomes`.`user_id` = 1 and `incomes`.`user_id` is not null

You can see in the image above that this query is getting over 70,000 models and a lot of columns that we don't need. So, how do we improve this query. By doing this:

return Income::selectRaw("substr(date_income_received, 1, 4) as year")
        ->whereUserId($request->user()->id)
        ->groupBy('year')
        ->pluck('year');

The SQL statement for this is as follow:

select substr(date_income_received, 1, 4) as year from `incomes` where `user_id` = 1 group by `year`

Note: I originally had ->select([DB::raw('YEAR(date_income_received) as year')]) in the query but this made my tests fail due to YEAR() being incompatible with SQLite. After trial and error, I found substr() to be a good alternative. Although I think I'm planning to revert back to using MySQL for my tests, and in turn, I'll switch it back to YEAR()

This snippet of code takes 338ms and only uses 6MB of memory 🤯. That's a massive improvement and we're getting the same result.

Initial Query:
Over 2 seconds, 203MB of memory.

Improved Query:
338ms and only 6MB of memory.

Conclusion

When writing queries it's always a good idea to have a way to see the performance of your query so that you can ensure that your writing optimal queries. It's easy to do something as simple as $request->user()->incomes()->get(), but as you can see from above this is not an optimized query and we can do better.