Tag Archives: PHP

How to Use `USE INDEX`, `FORCE INDEX` MySQL in Laravel Eloquent

Using Laravel Eloquent become one of the code standards in my organization. But as we all know sometimes using ORM like Eloquent limits us from using advanced SQL features.

Here is my experience when I faced an Eloquent query that was very dynamic and required me to tell MySQL to use a specific index for the query. To do so, basically I just needed to add the USE INDEX function. However, Eloquent does not come with this functionality by default.

So in order to do it I add a scope in my model like below:

// app/Models/User.php

use Illuminate\Contracts\Database\Eloquent\Builder;

public function scopeUseIndex(Builder $query, string $index): Builder
{
    return $query->from(DB::raw("{$this->getTable()} USE INDEX({$index})"));
}

This way I can just call

User::useIndex('my_index_name');

Thats it! 🤭🤭