Now that you know how to apply an index to drastically improve the performance of your queries, let's take it a step further. So at the moment we're fetching all records, or all videos, that were downloaded by this user. But you'll notice that it returns everything, even videos downloaded back in 2016. Maybe I only care about the downloads from this year, 2019. We might say, let's append to it, give me all videos downloaded by this user, and also where the downloaded_at is between the beginning and end of 2019. So we'll start in 2019, January 1st, and then I'll copy this, and then we'll just do 2020. So give me any videos by this user that were downloaded at some point in 2019.

So again, how is this possible? How is it able to reduce the range by so much? Well, remember, the answer is right here. You already have an index on user_id. We applied that in the previous episode. So when we run this query, before we even get to this point, the database knows, okay, I have all these records from all these users, but I'm only interested in this user. So I'm going to discard everything else and only search in those records. So notice, if we have 21 records here, that's going to match up exactly with the number of records total for that user.

So notice, if we have 21 records here, that's going to match up exactly with the number of records total for that User. So if we run it, there's 21 rows total. That's why, even though we didn't have an index on DownloadedAt, it's still incredibly fast. However, this should raise a flag. If this query is incredibly fast because of the userId here, what if you needed all videos downloaded in 2019, period? It's not scoped to a particular User. All right, well now, we've lost that userId index, and we're no longer able to reduce

effects on performance, so you always need to be thinking about this. And you know what? It even gets worse. So let's say, for your business, you want to fetch all Users as well as the total number of videos they downloaded in 2019. Okay, let's try it out. So I know I want the userID and the total count of videos they downloaded between these two dates, so effectively 2019. So if we run this, we already knew it was going to fail, and that's because we have an aggregate here, but we haven't specified how we want to group.

So if we run this, we already knew it was going to fail, and that's because we have an aggregate here, but we haven't specified how we want to group. So let's say I want to group everything according to the user_id. And if we give it a run, notice how long this takes. Three, four, five, six, seven, oh my goodness, nine, ten, this is insane! Twelve seconds in order to run that query. And think to yourself, this is something you might run as an Eloquent query and you don't even think about it. But then you run it in the browser and you notice that it lags and lags to the point of twelve seconds just to generate this information here.

So as a result, we have to hunt through every single record in the database, which is incredibly slow. So now we think to ourselves, all right, we got to improve this. It sounds like we need an index on DownloadItAt. Okay, so we switch back and we're going to add it now. Give that just a second. And now, pay attention, if we run this query again, it took less than a millisecond. And if we run EXPLAIN on it, you'll see now we have a type of range, so a range of records to search through, and specifically that range is sixteen rows. So we went from 12 seconds to literally less than one millisecond.

That would be the recommended way, but you might do year instead. If you run it, notice everything changed. Type is back to index, and we're back to searching all of the rows, which means if we try it, even though we're going to get the exact same information, we're back to 10, 12 seconds for it to run. So be careful. From personal experience, these are the sorts of things that will bite you. You may think, well, it's the exact same thing. No, it's not. This is actually the result of a function call.

No, it's not. This is actually the result of a function call. Just think of it that way. You're not looking at the downloaded at column. You're looking at the result of a function call, and we can't have an index on that. So that's why any book you read, it's always recommended to handle your dates in this way. That way, the query can look at the column itself rather than the result of a function call. If we bring it back to how it was before, notice how this and this will change as soon as I rerun it.