Laravel Database Optimizations: Rewrote Laravel Eloquent Queries Into An Efficient And Optimal Code
I rewrote database queries for more efficiency and performance. My web app is in Laravel using Laravel Eloquent as an ORM. Let’s explore what I optimized one by one!
getting previous 30 days views 🔗
I originally wrote this code:
// get prev 30 days w/o today
// cache for 1 day
$prev30days = cache()->remember('prev30daysviews', 86400, function () use ($today) {
return Pageview::query()
->latest()
->take(31)
->get()
->sum('views') - $today->views;
});
Here is the code without caching:
$prev30days = Pageview::query()
->latest()
->take(31)
->get()
->sum('views') - $today->views;
Rewrote the code using query builder, index-backed dates, and native database summation:
// with caching
$prev30days = cache()->remember('prev30daysviews', 86400, function () {
return Pageview::query()
->where('created_at', '>=', now()->subDays(30)->toDateString())
->where('created_at', '<', now()->toDateString())
->sum('views');
});
// without caching
$prev30days = Pageview::query()
->where('created_at', '>=', now()->subDays(30)->toDateString())
->where('created_at', '<', now()->toDateString())
->sum('views');
Another rewriting using query builder, with taking latest 30 and database native sum:
// with caching
$prev30days = cache()->remember('prev30daysviews', 86400, function () {
return Pageview::query()
->latest('id')
->skip(1)
->take(30)
->sum('views');
});
// without caching
$prev30days = Pageview::query()
->latest('id')
->skip(1)
->take(30)
->sum('views');
The won implementation is the date-based query.
summing preceeding 30-day views 🔗
A similar Laravel code, here is my original implementation:
// cache for 1 day
$prec30days = cache()->remember('prec30daysviews', 86400, function () use ($prev30days) {
return Pageview::query()
->latest()
->take(60)
->get()
->sum('views') - $prev30days;
});
// original without caching
$prec30days = Pageview::query()
->latest()
->take(60)
->get()
->sum('views') - $prev30days;
And here is how I optimized it using data-based querying with index-backed dates for better performance and efficiency:
// optimal without caching
$prec30days = Pageview::query()
->where('created_at', '>=', now()->subDays(60)->toDateString())
->where('created_at', '<', now()->subDays(30)->toDateString())
->sum('views');
// optimal with caching
$prec30days = cache()->remember('prec30daysviews', 86400, function () {
return Pageview::query()
->where('created_at', '>=', now()->subDays(60)->toDateString())
->where('created_at', '<', now()->subDays(30)->toDateString())
->sum('views');
});
views of today 🔗
Getting today’s page views, my original implementation:
// original impl w caching
$today = cache()->remember('today', 300, function () {
return Pageview::whereDate('day', Carbon::today())->first();
});
// original impl wo caching
$today = Pageview::whereDate('day', Carbon::today())->first();
The optimal implementation using data-based querying and returning the need column only:
// optimal impl wo caching
$today = Pageview::where('day', today()->toDateString())->value('views') ?? 0;
// optimal impl w caching
$today = cache()->remember('today', 300, function () {
return Pageview::where('day', today()->toDateString())->value('views') ?? 0;
});
views of yesterday 🔗
The original old implementation of views yesterday was:
// cache for 1 hr
// original impl w caching
$yesterday = cache()->remember('yesterday', 3600, function () {
return Pageview::whereDate('day', Carbon::yesterday())->first();
});
// original impl wo caching
$yesterday = Pageview::whereDate('day', Carbon::yesterday())->first();
Now, the new efficient implementation is:
// optimal impl w caching
$yesterday = cache()->remember('yesterday', 3600, function () {
return Pageview::where('day', now()->subDay()->toDateString())->value('views') ?? 0;
});
// optimal impl wo caching
$yesterday = Pageview::where('day', now()->subDay()->toDateString())->value('views') ?? 0;
count cards 🔗
The original OLD implementation for counting cards:
// original w caching
$cards_count = cache()->remember('cards_count', 3600, function () {
return Card::count();
});
// original w caching
$cards_count = Card::count();
Here is the new optimal implementation:
// optimal w caching
$cards_count = cache()->remember('cards_count', 3600, function () {
return Card::query()->count('id');
});
// optimal wo caching
$cards_count = Card::query()->count('id');
The final thoughts 🔗
The main ideas for making Laravel Eloquent queries more efficient is:
- doing things in database engine (database-native processing) rather than in PHP/Laravel,
- retrieving only the columns needed,
- prefer using date-based queries; but make sure these dates are indexed.
I hope you enjoyed reading this post as much as I enjoyed writing it. If you know a person who can benefit from this information, send them a link of this post. If you want to get notified about new posts, follow me on YouTube , Twitter (x) , LinkedIn , and GitHub .