Optimize Queries for performance

Hi devs,

Am working on improving my app speed and I have a method below, how best can I optimize it for performance

public function index()
    {
        $newProducts = Cache::remember('newProducts', now()->addSeconds(30), function() {
            return Product::orderBy('created_at', 'DESC')->with('category', 'photos')->take(5)->get();
        }); 

        // $products = Product::with('category', 'photos')->inRandomOrder()->take(8)->get(); 

        $slides = Cache::rememberForever('slides', function() {
            return Slide::all();
        }); 

        $banner = Cache::rememberForever('banner', function() {
            return Banner::where('group_id', 1)->orderBy('order', 'DESC')->get();
        });

        $middleBanner = Cache::rememberForever('middleBanner', function() {
            return Banner::where('group_id', 2)->orderBy('order', 'DESC')->get();
        });

        return view('front.welcome', compact('slides', 'banner', 'middleBanner', 'newProducts'));
    }

Snapey

5 years ago

Level 122

use debugbar to see what db queries are actually being run. You have cached all the models here, but if you are loading a related model in the view then you have other db queries and possibly also n+1 issues.

Not sure the benefit of caching just 5 records for just 30 seconds.

Only load the columns that you use in the view.

Check you don't have other queries in partials or view composers.

1 like

chaudigv

5 years ago

Best Answer

Level 16

Laravel Debugbar

I highly recommend using barryvdh/laravel-debugbar (GitHub). This will tell you exactly how many queries were fired, how much time each one took and much more.

select()

Use select() alomost everytime. Slide::all(); is gonna select all columns. If created_at, updated_at... plus other columns that are not needed then do not include them in the query. Each data consumes your memory, and memory is limited. Instead do Slide::select(['id', 'title'])
When using relations, you can select columns by doing Product::select(['id', 'category_id'])->with('category:id,name') // make sure to select primary and foreign key to build relationship.

Indexing

Indexing is another solution. Since you are doing where() + orderBy ... compound indexing on groupId + order columns will benefit you.
Check out sample videos from Jonathan Reinink

Cache

When caching, don't use get() (just my preference) because it returns a lot of data which adds up to the caching size. Rather use ->get()->toArray() and collect() the cache.

$slides = collect(Cache::rememberForever('slides', function() {
            return Slide::get()->toArrya(); // convert to array
        })); // collect() the cache

2 likes

sr57

5 years ago

Level 39

Very BEST answer :-) Thanks @chaudigv

1 like

Please or to participate in this conversation.