You see, databases don't always return data back in the exact same order. I've seen this firsthand where a customer was not able to see certain records in their database simply because the order of those records changed while navigating from one page to the next. In fact, if we look at our query in the Laravel debug bar, it even gives us a warning about this. Limit without orderBy causes non-deterministic results depending on the query execution plan. So whenever you're using pagination, be sure to include an orderBy statement. Let's update our app. Let's go to our UsersController and add an orderBy to our query.

Let's update our app. Let's go to our UserController and add an orderBy to our query. We're first going to orderBy the lastName and then we're going to orderBy the firstName. Now let's take a look at this in the browser. Note, we're currently at about six milliseconds combined query time. Plus we can see that our User query here doesn't have any orderBy statements. Now if we hit refresh, we can see that the query now includes two orderBy statements and we can see that our records are nicely being ordered. However, if we look at our combined query time again, you can see that we're up to almost

and we can see that our records are nicely being ordered. However, if we look at our combined query time again, you can see that we're up to almost 95 milliseconds. That's a pretty significant increase. We must need some indexes on these columns. Let's add those. Let's go to our users migration and add an index to both the first_name and last_name columns. Now let's rerun our migrations in php artisan migrate --fresh --seed.

Now let's rerun our migrations in Cedar. php artisan migrate:fresh --seed. Now let's go back to the browser and see if this has had any impact on our performance. Again, we're currently at roughly 95 milliseconds. And if we hit refresh, we're still at roughly 95 milliseconds. Why hasn't this helped? Let's copy this query and jump into TablePlus to see if we can figure out what's going on. Let's paste it in and run an EXPLAIN on it. If we look at our possible keys and our key columns, we can see that this query is not using either of our new indexes.

If we look at our possible keys and our key columns, we can see that this query is not using either of our new indexes. What's more, if we look at our extra column, it says it's using file sort. That's not good. This basically means that MySQL was unable to use an index to sort this data and instead had to read each row and sort them. And that's a slow operation. So why isn't our query using our first and last name indexes? Let's experiment. Let's try to remove one of the columns in the order by statement.

Let's experiment. Let's try to remove one of the columns in the ORDER BY statement. Let's remove the first_name ORDER BY first. And if we rerun this, we can see that our last_name index is now being used. Let's try it the other way around. Let's add the first_name back. And this time, let's remove the last_name. And if we run that again, we can see that it's now using the first_name index. So on their own, both indexes are used in our ORDER BY statement. However, together, they're not being used.

So on their own, both indexes are used in our orderBy statement. However, together, they're not being used. This is a great candidate for a compound index. A compound index, also known as a composite index, is simply an index that contains more than one column. Let's add that. Let's go back to our users migration and remove the two indexes that we added on the first_name and last_name columns. And instead, let's add a new compound index for those columns. And we pass in an array, which includes the last_name and the first_name.

And instead, let's add a new compound index for those columns. And we pass in an array, which includes the lastName and the firstName. Now, let's rerun our migrations in php artisan migrate:fresh --seed. Now, let's go back to the browser and see if this has had any impact on our performance. Again, we're at roughly 95 milliseconds. And if we hit refresh, we're back to roughly 6 milliseconds. Nice. Now, before I wrap up this lesson, I want to show you one more important thing to be aware of.

Now, before I wrap up this lesson, I want to show you one more important thing to be aware of. Let's go back to our query, but this time let's sort by the firstName first. And now if we go back to the browser and hit refresh, we're right back up to almost 95 milliseconds. So this is an important thing to be aware of. When you're using a compound index in an orderBy statement, the order of the columns in that compound index matters. So if you did want to order by the firstName first, simply reverse the order of the columns in the compound index.

So if you did want to order by the firstName first, simply reverse the order of the columns in the compound index.