The site is completely down Database. CPU usage is at a hundred percent and every page fails to load support. Tickets are piling up. And the most terrifying part is that nothing changed. There was no new code pushed out, there's no configuration change, nothing. The site just stopped working And it turns out someone just searched for widget. That's it. Just a simple search.

And it turns out someone just searched for widget. That's it. Just a simple search. And we can see that that's taking a while. And thanks to the Laravel debug bar, we can see that that took almost four seconds. Now that doesn't sound like a lot. And, and let's face it, if this was our only user, then it'd be annoying, but it, it'd be manageable. But, you know, the problem is that we don't have just one user.

But, you know, the problem is that we don't have just one user. This is just one query running, and the database was locked up while it was running. So if we had multiple users making similar queries or just doing normal database stuff, our database is gonna get overloaded. And you know, the problem is really our query, because what are we doing here? We are looking for products that have a name of query,

because what are we doing here? We are looking for products that have a name of query, but it's not just the query that we provided. It's a full wild card. We have a wild card at the beginning and a wild card at the end. And it's not just for the name, it's also for the description. Now, I fully understand that when it comes to searching, we need to provide, you know, a, a usable search mechanism.

Now, I fully understand that when it comes to searching, we need to provide, you know, a, a usable search mechanism. But the problem with this approach is that this then becomes a full table scan, because you could have indexes built on description or name or whatever you want, but the minute that you have a double wild card query, the index goes out the window. Because, you know, think of it as a phone book. If you want to find Sam, yeah, I know it's done

Because, you know, think of it as a phone book. If you want to find Sam, yeah, I know it's done by last name, but if you wanna find Sam, you go to the S'S and then you look for Sam, right? But if you have a wild card at the beginning of Sam and a wild card at the end of Sam, it doesn't know where to start. So it starts at the beginning and just scans the entire table. And that's what happens. That is not great,

and just scans the entire table. And that's what happens. That is not great, but that's also just, you know, part of it. But really the, the thing here is the limit, or rather the lack of a limit. We have no limit on this query. And this is going to be, you know, something that we would want to put a limit on because this is a search. So we want to put a limit.

because this is a search. So we want to put a limit. We also want to paginate it because all we are doing is just fetching all of those things. But then, you know, there's another problem. What if all we do is search for E, it's gonna fail. The site's gonna right there, it goes down because E is very common. So, and it's not just that we would search for E, it's

because E is very common. So, and it's not just that we would search for E, it's that we allow someone to search for E. So we need to put limits on, you know, the query that is going to be supplied. So there's a few things that we can do to fix this, and we're gonna start with that. So before we perform any kind of query, we are going to check the length of the provided query. If it's less than three, then we don't want to even try

to check the length of the provided query. If it's less than three, then we don't want to even try to search for that because there's no sense in that. So we're gonna go back with an error that says, well, what do we wanna say that the search term must be at least three characters. I, I think that's fair. We might want to bump that up to five. I don't know. But no, because you know what, if someone wanted to search for pro, because pro is in some of the names,

But no, because you know what, if someone wanted to search for pro, because pro is in some of the names, so we'll, we'll stick with three. So that's the first thing that, that's gonna save us from, you know, this mess right here. But then we need to address our query. Now, you know, any reasonable search is going to allow wildcards here. So we need to think in terms of allowing this. So what we want to do then is essentially limit

So we need to think in terms of allowing this. So what we want to do then is essentially limit what we are going to pull in, which we could do right here. So let's say that we're gonna limit 100, and then we're gonna paginate, uh, the default's 15, let's do 20. So with that change alone, you know, the, this is going to be a, a very good fix there. So what was that? Almost four seconds for widget. So now if we search for widget,

So what was that? Almost four seconds for widget. So now if we search for widget, we will have a much faster result. Now, of course, we are only seeing 20 of those, but that's where the pagination comes into play. So we don't have to worry about that. We are paginating results and everything's gonna be faster. So now we get from four seconds to 200 milliseconds. And you know, that's good, but we can make it better because, you know, search is one of those things that a lot

And you know, that's good, but we can make it better because, you know, search is one of those things that a lot of people are gonna do, and a lot of people are going to search for the same things. So wouldn't it be great if we cashed the results? Well then here's what we can do. We can create our own little cache key here, and we're gonna base it upon search, and then, uh, we will include the query. But, you know, if we're gonna paginate,

and then, uh, we will include the query. But, you know, if we're gonna paginate, we should also cache the page too, shouldn't we? So let's do that as well, which means that we need to get the page, which we can get from the request. I don't remember what we are getting here. What do we get? We get the request, okay? So we can say request input page. So we will have the search followed by the query and then the page.

So we will have the search followed by the query and then the page. Now of course, I'm, I'm being incredibly naive with the, the search query and the page. You know, all of these are coming from the, the user we would need to sanitize and, you know, make sure that we are working with good values here. But for the most part, we're just gonna stick with this. So we have our cash key, which means

But for the most part, we're just gonna stick with this. So we have our cash key, which means that we can now say products equals, we'll say cash, remember. And then we will use our cash key. Let's say that we want this to live for 300 seconds. Then we will have our callback that we'll use our query. Okay? So then we'll just take our products here, lift that out, and we will return our products.

Okay? So then we'll just take our products here, lift that out, and we will return our products. And yep, maybe that'll work. Maybe that'll work. Of course that'll work. So we pass that on down to the view after we log all of those things. And there that is. So if we go back, let's just start all over again. So the first query is gonna be for, you know, whatever it is. And that is of course, gonna hit the database.

you know, whatever it is. And that is of course, gonna hit the database. We can see that that was 222 milliseconds, but any subsequent search is gonna be faster, 168 milliseconds than a hundred fifty, three forty five milliseconds. We get the same results because we have cached this. But of course, if we went to search for something else like pro, then once again we hit the database 153 items or,

for something else like pro, then once again we hit the database 153 items or, or milliseconds, sorry. But if we search again, it's dramatically faster because we are serving cached results. So the lesson in all of this, you know, every query that accepts user input is a potential denial of service attack. It's not that the user has some malicious intent, it's just that, you know, by code that looks normal

It's not that the user has some malicious intent, it's just that, you know, by code that looks normal and looks okay, could result in, you know, a very, very inefficient query that could crash the site. So, limit results, paginate results, cache results. It's better for our application and most importantly, it's better for the end user.