In the last lesson, we wrote a search scope that allows us to filter Users by their first name, last name, or company name. We then ran a search for the term Bill Gates Microsoft and saw his record show up. However, when we looked at our queries tab in the Laravel debug bar, we saw that our combined query time was 326 milliseconds. This feels a little slow to me. In this lesson, I'd like to introduce some indexes to see if we can improve the performance of this query. Generally, as a starting point, I'll simply add indexes to the columns that are being operated on. In our case, that means adding an index to the User's first name, last name, and the company name. Let's do that. We'll start by going to the companies migration, and then we'll simply add an index to the name column. Next, we'll do the same thing for the users migration. In this case, we'll add an index to both the first_name and the last_name columns.

and then we'll simply add an index to the name column. Next, we'll do the same thing for the users migration. In this case, we'll add an index to both the first_name and last_name columns. Now, let's rerun our migrations and our seeder. php artisan migrate --fresh --seed. Now, let's go back to the browser and see if this has had any impact on our performance. Notice that we're currently at a combined query time of 326 milliseconds. And if we hit refresh, you can see that it's changed to 329 milliseconds. Interesting. So adding indexes has not made any difference at all. Let's confirm that they've actually been added. In TablePlus, we can go to the companies table and verify that this index has been added. And sure enough, we can see the company_name index has been added on the name column. Let's look at the users table as well. Again, we can see both of our new indexes have been added. So why hasn't this helped? In situations

company name index has been added on the name column. Let's look at the users table as well. Again, we can see both of our new indexes have been added. So why hasn't this helped? In situations like this, I like to copy the query generated by Laravel and experiment a little in TablePlus. Let's go to the browser and copy this generated query. Let's paste this query into TablePlus and format it so it's a little easier to read. And the first thing we'll do here is just run and explain on this query. And if we look down here at the results, let's pay special attention to the possible keys and key columns. The possible keys column represents indexes that potentially can be used in this query. The key column represents indexes that are actually being used. So you can see that our new indexes here for the users' first name, last name, and company name are not being used at all. The only index that's being used is the primary ID on the company

So you can see that our new indexes here for the user's first name, last name, and company name are not being used at all. The only index that's being used is the primary ID on the companies table. One of the reasons for this is because of the wildcard prefix that we have in our search terms. MySQL is unable to use an index if a search term starts with a wildcard symbol. So let's start by removing that. Now let's run our query again. So that change has definitely helped. We can now see our first name and last name indexes as possible indexes for this query, although it's still not being used. We can also see our company name index as a possible index for our company's subqueries. Again though, it's not being used. So what's preventing our query from using all of these indexes? This can be one of the most challenging and frustrating questions to answer when optimizing your database queries. In my experience, the best thing to do at this

to use our indexes. We need to remove the wildcard prefix and then we need some solution for the company's subquery since we don't want to just remove it. Let's start by fixing the wildcard issue. Let's go back to our User model and just remove the wildcard prefix. Now it's important to realize that this will limit our search ability to only columns that start with our search term. Typically this is actually fine since this is generally the way we search anyway. However, it does introduce a weird edge case. We can no longer search for a multi-word company such as Microsoft Corp. And this is because our query first looks for Microsoft, which it finds a match for at the beginning of the company name column. And then for the term Corp, which it of course cannot find a match for at the beginning of the company name column. The way I like to solve this is with search groups where you can add quotations around the parts of your search query that you

cannot find a match for at the beginning of the company name column. The way I like to solve this is with search groups where you can add quotations around the parts of your search query that you would like to keep together as one term. And it turns out there's actually a very simple way to implement this in php using the str_getcsv function. Let's simply replace our explode with str_getcsv and then change our arguments to terms, a space for our delimiter, and quotes for our enclosure. Okay, let's test this out. If we run the query again, we can see that we're now getting Bill Gates from Microsoft back in our results. And if we look in our search query here, we can see that it's grouping Microsoft Corp together as one search term. So that's great. We removed the first barrier that's preventing our query from using our indexes. However, as you can see, our combined query time is still at 275 milliseconds. And that of course is because

We removed the first barrier that's preventing our query from using our indexes. However, as you can see, our combined query time is still at 275 milliseconds. And that of course is because our company subquery is still preventing our indexes from being used. In the next lesson, we'll look at how we can solve that.