Laravel: How to use `USE INDEX` and `FORCE INDEX` for Eloquent and MySQL

Eloquent - is a powerful class for working with databases, easily extensible and easy to use. But sometimes it is necessary to get a little more than the developers have provided for at the moment.

Some time ago I was investigating a problem with MySQL indexes on a big table and realized that there is no way to build a query with use index or force index statements using Laravel Eloquent class.

This is why I have tried to fix it :)

Let me introduce a simple Laravel package to cover these needs:
https://github.com/vpominchuk/laravel-mysql-use-index-scope

Installation

composer require vpominchuk/laravel-mysql-use-index-scope

Usage

Simply reference the required trait in your model:

Model

use VPominchuk\ModelUseIndex;

class MyModel extends Model
{
    use ModelUseIndex;
    ...
    ...
}

Anywhere in the code:

$builder = MyModel::where('name', $name)->where('age', $age)->
        useIndex($indexName)->...

MySQL Indexes

Don't forget to create a named index with Laravel migration:

$table->index(['name', 'age'], 'user_age_index');

Available methods

useIndex($indexName)
Tells MySQL to use an index if it possible.

forceIndex($indexName)
Force MySQL to use an index if it possible.

Please check my personal blog for new articles:
https://pominchuk.com/posts