Vollständiges Transkript anzeigen (2.396 Wörter)
Hello guys, this video will be optimization of performance of this fictional home page for gaming website where you can browse games by category. This is fake data, but basically a lot of data on the home page of e-commerce. So a lot of products with their relationships and it may load for quite a while. In this video I will go through various steps of optimizations with Eloquent queries, data loading, caching and everything in between. So in the beginning we have 100+ database queries, almost a second of data to load and this is based, by the way, the idea came from I was browsing Upwork and some of the jobs here on Upwork I see are about performance optimizations or general fixing Vibe coding as well. This was one of the jobs with Next.js in this case, but I just took the idea, not the implementation, so front-end Next.js, back-end Laravel. Also, I didn't use any Meilisearch or any other API integration, but one of the optimization requirements was slow loading, long initial load times, slow response handling, slow page loading. And I decided to implement similar project like gaming which is described here, similar to gaming marketplaces such as Eneba. And let me show you the before and the after in this video, step-by-step. Let's dive in.
So our starting point is the home page of e-commerce project. It's about gaming in this case with things like platforms and stuff like that, but the photos are of course fake, not about gaming. And what do we do first to measure the things, the performance on the website is to install some tool to measure. My first personal choice is still the good old Laravel Debugbar, you can see that at the bottom. This is the package here, it works still until this day, still fastest choice, but you can also use Laravel Telescope, Clockwork or whatever you think is best. And here immediately we see the amount of database queries and the home page load is almost a second. So let's start fixing those N+1 queries. First, we need to enable the detection of N+1 query and this can be done globally in the AppServiceProvider. So in the boot method, you add this function `preventLazyLoading` and only not in production, this is important and model on top. Now, what happens if we reload the home page? We reload and we should get an exception exactly as planned. So whenever Laravel detects N+1 query, it will throw an exception and then you can debug and fix it. Important to enable that for all environments except production, because you don't want 500 errors to happen on production server for live users. You better have performance issues there but not 500s that the page wouldn't even be working. So yeah, what do we fix here? So we have featured with heroes and images, and I guess the images are not eager loaded. And in the controller, I discovered that there's products without images and without more things that were not eager loaded. So the fix is to have with method of course, loading the relationships. So that's the easy part. Now, I refresh the page and I get the same exception, but in another use case, category children, so another N+1 query and in another place in controller, add it with method. Refresh and another N+1 query in the blade where we have PHP block. By the way, PHP blocks in blade is not my favorite thing, and I deliberately generated this piece of code to showcase that, because more and more I see PHP blocks in Livewire specifically and that strict logic with MVC is not that strict anymore among Laravel developers that I've noticed. So you may find N+1 query in the blade as well, but of course the fix is not in the blade because it's just showing the information. The fix is in the controller, adding another with statement. So we refresh and we don't have N+1 queries anymore. The page is loaded successfully now without any exceptions with wow, 29 queries, great result, but look at the time, it's still 700 milliseconds, better but not ideal.
So we continue with the optimization and the next one I will show is how much data are we loading from the database. So the thing that I added just recently, category with children, I fixed N+1 query but we don't need the full children records. If you see how those categories are used on the front page, it's just the amount of subcategories. We don't need all the record of subcategory. So we have categories children count, but it can be changed to children count here and with count in the controller, and now the database query will change. Now we have select asterisk from categories, we refresh the page, that still works, as you can see, we don't even have that subquery for subcategories anymore, we have one query, select categories with select count of children count instead of all children records. And also another optimization related to count and averages. I already did that behind the scenes, so these ratings as you can see, the star ratings like the average and the amount of ratings for products. This is how it looks, so product order by descending sales count, or order by rating average, or something similar and you don't see with count here or with count here, because often it makes sense to aggregate the data in the database directly, so whenever some rating comes up or sale comes up, you increase or recalculate that data directly in the products database table. So you aggregate the data not every time that the people are loading the home page on every product, but the data is already in the database aggregated once. Yes, the database table then it's bigger in terms of amount of data, but usually such optimization does pay off, it's kind of like caching but on a database row level. Similar thing how we load the products here bestsellers, top rated, and others, we don't specify the select of the columns, so as the comment says, select asterisk everywhere. And the product table may contain long descriptions, long text somewhere, including those publisher, categories, those could contain also long columns, so we download too much data to the home page. Let's fix that one. And look at the changes I've made behind the scenes. So yes, I did add select, but look at that, this is a variable because we have multiple queries for the same thing or similar thing, so I have this array, kind of a constant in the beginning of that method, and in some cases we need to add a short description for featured products, otherwise we need just those columns. And also same for relations, with product relations, this is the default, but if we need to add something on top, we may also add it if we need it, but by default in this case those are always enough. So load only the columns that we do need in the blade. So the queries used to be like select asterisk from products, from publishers, from products and publishers again, and we had 37 megs of RAM, this is all in the RAM memory. What if we refresh now? We have 35 megs of RAM which is not a huge improvement because we have like only like 20 to 30 products, so it's not a big deal, but it's more like a hygiene better practice to have only the columns listed that we need. And now we're down to 620 milliseconds, which is still not ideal, let's continue.
So now after we optimized a lot of queries and data loading, the question is, do we even need those queries because a lot of that data is pretty static so it doesn't change every second or even every minute. So best sellers could be cached for at least like 5 minutes or maybe even full day. Of course the time for caching and even possibility for caching depends on the situation, maybe the price changes every second, who knows, it depends on the project and the data. But let me show you my implementation and we will see what would be the loading of this website after caching. And this is the updated controller, caching is pretty sophisticated here because we have a lot of similar data points. So this is one query now. So we have product query select as it used to be, but now we have two extra things. First, after get we map every product with internal private function I will get to that in a minute into array and then we have cache remember, we cache that data with specific key for featured section, it won't be the same for bestseller cache section but the same will be or maybe cache time. And those are both configurable, cache TTL minutes is on top in the same controller and then cache keys are in the product eloquent model. Of course this is only one way of doing that but constants on top of product model. Those are constant just to avoid mixing the strings and making typos or silly mistakes so that's why the constants and we should use only the constants instead of direct strings, and similar things for best sellers and top rated again. Get the data, map, and then cache. Now let's see what's inside of that product card data. This is basically getting the details adding or not adding short description depending on the scenario, that's why this is a parameter and then return the array which then needs to be cached. So this function is a private function in the controller for specific that scenario because as I said we have a lot of overlapping or similar data structures. Then also there was another query for hot deals, so another cache remember with another different cache key but for the same 5 minutes. Also cache remember for homepage categories for homepage platforms. Those are very quick queries to be honest to the database but those are even more rarely changed at all. So why bother querying them from the database in the first place? And finally the last cache for another query for home page stats. And then in the blade we have a few changes related to how we show the data, so we have arrays here in this case now. So everywhere we had that data for products including product card component which has product cover URL, now also has arrays now, so hero is also now hero name and short description are also arrays. And the last part of that equation is when the cache should be cleared. It's cached every 5 minutes by default with that parameter as I showed but also we need to invalidate the cache if something important changes. And this is of course again very individual, depends on the project, but general strategy is to have observer, so in the same product model on top, probably, yep we have observed by which is new syntax of Laravel 13 or 12, I don't remember, I think it was released still in Laravel 12 and then Laravel 13 implemented more PHP attributes. So anyway we have product observer and then on every created, new product, updated product and deleted we have forget homepage product cache and this is another benefit of constants in the model we have for each of those constants and cache forget and those constants are here. So not only we have individual constants, we have array of going through them, also as constant as well to be used in observer in such for each loop. So then the data stays in the cache either for 5 minutes or until some product is changed from admin panel or some import from wherever with eloquent model. And now let's try to reload the home page I will do that in a separate browser tab, so we will see those results still for comparison and I refresh now and nothing really changes first time. In fact we have more queries 46 because we have delete from cache, select from cache and insert into cache. In this case I'm using database driver for caching. Now if we refresh again, look at that, select from cache and 248 milliseconds. We have 10 queries from the database and all those queries, almost all of them are related to cache so we don't load product database table at all. So yeah, these are kind of easy wins that you can optimize on eloquent level and on Laravel level and with caching which already gives better performance, but of course you can dive much deeper into for example rerendering full HTML in cache, also using separate packages like Spatie Response Cache. Also of course the load of this home page depends on the image loading speed and of course this home page is just Laravel which loads all at once and if you use something like React or Livewire whatever with dynamically loaded data for each of those blocks that also maybe optimization because the home page would load at once with static HTML almost and then would load the data in like points of a second separately with placeholder, so that maybe even a better UX. Let me know in the comments below if I should implement that and maybe shoot a separate video on that. But for now for this video this optimization I showed is kind of table stakes and I think in majority of cases this optimization will already give great results. And if you want to see more of such eloquent optimizations or syntax interesting options I have a course on Laravel Daily, Eloquent Expert Level updated to Laravel 13 pretty recently. So kind of small lessons here and there, 2, 3, 4 minutes of video with quick tip on certain syntax of Eloquent. So you can pick which lessons to watch and expand your Eloquent knowledge for more effective work with your database in Laravel projects. I will link that course in the description below. That's it for this time and see you guys in other videos.
Video
