In our previous lesson, we added indexes to the Companies and Users tables to try and speed up our search query. However, we learned that the query was unable to use those indexes. We were able to fix the first issue that was preventing this by removing the wildcard prefix from our like queries. However, we still had to solve the second issue, and that's our company subquery. In this lesson, we're going to try and do that by exploring faster options than orWhere has. So the first thing I like to try when a subquery is giving me grief is to try a join instead. Let's do that. Let's start by joining in the Companies table right at the top of our search scope. query, join, Companies, where the company's ID is equal to our userCompanyID. Alright, now that the Companies table is joined in, let's update our WHERE condition.

Query, Join, Companies, where the company's ID is equal to our userCompanyId. Alright, now that the Companies table is joined in, let's update our WHERE condition. So now we can just use a regular ORWHERE condition where we check if the company name is like our search term. Let's test this out. So note, we're currently at 275 milliseconds between all of our queries. And if we hit refresh, we're now at about 231 milliseconds. So that's helped a little. However, I suspect that we're still not using our user's firstName, lastName, and companyName indexes. Let's copy and paste this query into TablePlus and run an EXPLAIN on it. So we can see that our indexes are being listed in the possible keys column here,

Both of these approaches require us to check the company_id against the user's company_id. As a result, the companies table is essentially becoming a dependency of the user's table. Another way to tackle this problem is using a WHERE IN clause. A WHERE IN clause will let us remove this dependency between the tables. Let's try that. Let's start by removing the join, and then let's add our new WHERE IN clause. Or WHERE IN the company_id, and maybe in the past you've used this by passing in a precomputed list of IDs. However, in this situation, we're going to use a subquery instead. function, which is going to take the query builder,

However, in this situation, we're going to use a subquery instead. Function, which is going to take the query builder, and we're going to pass through the term, and then what we're going to do is we're going to select the ID from the companies table where our name is like our search term. Now what we're doing is running the exact same company subquery for every single row in the users table. Let's see if this has helped our query performance. With the join approach, we were at 231 milliseconds. Now, if we refresh this page, we're below 100 milliseconds.

With the join approach, we were at 231 milliseconds. Now, if we refresh this page, we're below 100 milliseconds. That's a pretty significant improvement. Let's copy and paste this query into TablePlus to see what's going on. And like before, we'll run an explain on this. And sure enough, if we look under our key column here, we can see that our company subquery is now able to use our name index. That's definitely why we're seeing this query run so much faster. The lesson here is to always try and remove unnecessary dependencies within your queries. Using the WHERE IN clause here accomplishes the exact same thing as our WHERE HAS.

The lesson here is to always try and remove unnecessary dependencies within your queries. Using the WHERE IN clause here accomplish the exact same thing as our WHERE HAS or our JOIN queries, except in a much simpler way. And that allowed the query planner to run more efficiently. Of course, this query is still not using our firstName and lastName index on the users table, even though it continues to show them as possible indexes. In the next lesson, we'll look at how isolating each query can help solve this.