Eloquent Performance: Faster LIKE searching

#Introduction to LIKE operator

The LIKE operator checks whether a string contains a specified pattern or not.

Usually, it is used with the percentage (%) wildcard to match any string of zero or more characters:

SELECT * FROM `companies` WHERE `name` LIKE '%clur%';

This example will match any company name that contains clur string:

• McClure Ltd
• McClure, Jaskolski and Nitzsche
• Sawayn-McClure
• Emmerich-McClure
• ...

Read more about LIKE operator here.

With one percentage at the end of the string:

SELECT * FROM companies WHERE `name` LIKE 'mc%';

This matches any company name that starts with mc:

• McClure and Sons
• McClure Inc
• McClure-Koss
• ...

Another example:

SELECT * FROM companies WHERE `name` LIKE '%sons';

This matches any company name that ends with sons:

• Schiller and Sons
• Feest and Sons
• Ankunding and Sons
• ...

#Indices with LIKE operator

Adding an index to the name field will make your search run faster:

CREATE INDEX `users_name_index` ON `users` (`name`);
CREATE INDEX `companies_name_index` ON `companies` (`name`);

In Laravel, you achieve the same thing by creating a new migration:

php artisan make:migration add_name_index

// migration

public function up()
{
    Schema::table('users', function (Blueprint $table) {
        $table->index('name');
    });

    Schema::table('companies', function (Blueprint $table) {
        $table->index('name');
    });
}

Do not use one migration for two different tables; I did that here to keep things simple.

There are two caveats when using the LIKE operator against a field that has an index:

1. The percentage wildcard at the beginning of the string will not use any indices; therefore, the users_name_index will not be used, which means a slower query.
2. Using whereHas and whereIn will ignore the users_name_index as well (I will discuss this later).

In this post, I will show you tips that make your LIKE searching faster.

Keep this in mind: using the percentage wildcard at the beginning of the search term will not use the searched field index (if any). In our case, the users_name_index.

With a small dataset, the difference is negligible. With thousands of records, this significantly impacts performance.

My database contains 10,000 companies and 100,000 users.

#Laravel Implementation

Take the following Laravel code in the App\Models\Company:

use Illuminate\Database\Eloquent\Builder;

public function scopeSearch(Builder $query, string $term = null)
{
    $query->where('name', 'like', '%'.$term.'%');
}

// App\Http\Controllers\CompanyController

public function index(Request $request)
{
    $users = Company::query()
        ->search($request->input('q'))
        ->paginate;

    return view('company.index', compact($users));
}

Looking at the debug bar, this takes 90 ms to execute.

Now, modify the scope:

$query->where('name', 'like', $term.'%');

Now it takes only 20 ms. That happened by removing the percentage from the beginning of the string.

This means MySQL is able to use the name_index on the companies table.

Be careful when using the percentage at the beginning of the string; it has a significant impact on performance.

#Searching in multiple fields within multiple tables

Sometimes you need to use the LIKE operator across two different fields in two separate tables.

For example, you want to search for either the user.name or user.company.name fields:

// App\Models\User
use Illuminate\Database\Eloquent\Builder;

public function scopeSearch(Builder $query, string $term = null)
{
    $term = $term.'%';
    $query->where('name', 'like', $term)
        ->orWhereHas('company', function($query) use ($term) {
            $query->where('name', 'like', $term);
        });
}

Because of the whereHas statement, MySQL will not use the user.name index, which means a slower query.

What about joining? Can you solve it with $query->join?

$query->join('companies', 'companies.id', '=', 'users.company_id');

The join introduces two problems.

First, MySQL still cannot use the users_name_index.

Second, since both companies and users have a name column, Laravel will override the $user->name with the $company->name.

Now try the whereIn clause:

$query->where('name', 'like', $term)
    ->orWhereIn('company_id', function($query) use ($term) {
        $query->select('id')->from('companies')->where('name', 'like', $term);
    });

Looking at the debug bar, the whereIn clause is much faster than whereHas and join.

Here is the benchmark (in ms):

• whereHas: 600 ms
• join : 500 ms
• whereIn : 130 ms

But the problem remains. MySQL still cannot use the users_name_index, even with the whereIn clause.

#Combine two LIKE queries

Sometimes, it is faster to run multiple queries than one query.

See how long this query takes:

SELECT * FROM `users` WHERE `name` LIKE 'ahmad%';

On my machine, it took only 2 ms, because MySQL used the users_name_index.

Now this query:

SELECT * FROM `companies` WHERE `name` LIKE 'ahmad%';

It took 2 ms as well. By running these as two separate queries, you achieve two things:

1. Much faster execution time.
2. Both users_name_index and companies_name_index are used.

Here is how to run these two queries in Laravel:

// App\Models\User

use Illuminate\Database\Eloquent\Builder;

public function scopeSearch(Builder $query, string $term = null)
{
    $term = $term.'%';
    $query->where('name', 'like', $term)
        ->orWhereIn('company_id', Company::query()
            ->select('id')
            ->where('name', 'like', $term)
            ->get()
            ->pluck('id')
        );
}

This issues two queries.

The first query (Company::query()...) fetches all the companies.id that match the given pattern:

# Took 24 ms on my machine
SELECT `id` FROM `companies` WHERE `name` LIKE 'ahmad%';

The second query fetches all the users.name that match the given query, and also uses the company ids from the first query:

# Took 5 ms on my machine
SELECT * from `users` where (`name` LIKE 'mc%' OR `company_id` in (267, 4563, ...)

By doing this, you ensure that both users_name_index and companies_name_index are used, which results in faster querying.

#Summary

• Leading % wildcards kill index usage -- LIKE '%term%' forces a full table scan. Use LIKE 'term%' whenever your use case allows it.
• whereIn outperforms whereHas and join for cross-table LIKE searches -- benchmark showed 130 ms vs 500-600 ms on the same dataset.
• Split queries to leverage indices -- running two separate indexed queries is faster than one combined query that cannot use any index. Two queries at 2 ms each beats one query at 130 ms.