laravel how to use force index in eloquent

Question

I have an SQL query that looks like:

SELECT * FROM orders FORCE INDEX(order_type_index) WHERE `type` = 1

I can use query builder to recreate this:

DB::table(DB::raw('orders force index(orders_type_index)'))
    ->where('type', 1)
    ->get();

But is there anyway to make Eloquent use this index, like:

Order::forceIndex('orders_type_index')->where('type', 1)->get();

Answer 1

You can do this by creating a local scope that changes the builder's table name when applied. The table should be checked for the index before it's used. You could throw an exception or ignore it if an invalid index name is supplied (I chose the latter approach.)

<?php

namespace App;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/*
 * Class Order
 *
 * @method \Illuminate\Database\Eloquent\Builder useIndex(string $index)
 * @method \Illuminate\Database\Eloquent\Builder forceIndex(string $index)
 */
class Order extends Model
{
    private function tableIndexExists(string $index): boolean
    {
        $table = $this->getTable();
        $index = strtolower($index);
        $indices = Schema::getConnection()
            ->getDoctrineSchemaManager()
            ->listTableIndexes($table);

        return array_key_exists($index, $indices);
    }

    public function scopeUseIndex(Builder $query, string $index): Builder
    {
        $table = $this->getTable();

        return $this->tableIndexExists($index)
            ? $query->from(DB::raw("`$table` USE INDEX(`$index`)"))
            : $query;
    }

    public function scopeForceIndex(Builder $query, string $index): Builder
    {
        $table = $this->getTable();

        return $this->tableIndexExists($index)
            ? $query->from(DB::raw("`$table` FORCE INDEX(`$index`)"))
            : $query;
    }
}

If you need to do this on multiple models, it can easily be added to a trait and imported. The docblock ensures that your IDE is aware of the magic methods for code completion.

Then you can use it like this:

$orders = Order::forceIndex("orders_type_index")->where("type", 1)->get();

// or this:
$orders = Customer::find(234)
    ->orders()
    ->forceIndex("orders_type_index")
    ->where("type", 1)
    ->get();

Answer 2

https://packagist.org/packages/shaburov/laravel-mysql-index-hints-scope

Order::useIndex('test_index')
->ignoreIndex('test_index')
->useIndex(['test_index', 'example_index']);