DEV Community

Cover image for Laravel: How to use `USE INDEX` and `FORCE INDEX` for Eloquent and MySQL
Vasyl Pominchuk
Vasyl Pominchuk

Posted on

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;
    ...
    ...
}
Enter fullscreen mode Exit fullscreen mode

Anywhere in the code:

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

MySQL Indexes

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

$table->index(['name', 'age'], 'user_age_index');
Enter fullscreen mode Exit fullscreen mode

Available methods

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

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

Discussion (0)