Force Index on a query

[danielbaylis]

I'd like to be able to push "FORCE INDEX (index_name)" into a query built with eloquent to force MySQL to use the correct index. I understand I can chain "->from(DB::raw('table_name FORCE INDEX (index_name)')" into a query but this is hardcoding a table name and doesn't seem laravel-esque given the query would break if the model/table name changed.

Having a forceIndex method that allowed an index to be specified (but not a table name) would ensure the query continues to follow the model's auto-detected table name.

I need this specifically to resolve an issue where MySQL is not able to find existing records when it tries to create an intersect between two indexes on the fly on a WHERE EXISTS sub-select like the one below. The issue is resolved by forcing MySQL to the correct index. Granted this is not laravel's problem but it would be handy (and tidy) feature.

select * from `messages` where exists (select * from message_routes where `message_routes`.`message_id` = `messages`.`id` and `owner` = ?) and `messages`.`id` = ?

[ahmedash95]

so you need something like Model::forceIndex('index_name')->get(); ?

[danielbaylis]

Yes. This would save needing to use DB::raw and hard code the table name.

[ahmedash95]

i will make a PR for it , hoping taylor merge it :D

[tomschlick]

Make sure to link back to this discussion so he can see the reasoning behind it. Seems reasonable to me 👍

[gocanto]

if I am not mistaking, you can achieve this performing using models relations, as so:

$users = Loan::has('installments')->with(['user' => function ($query) use ($q){
            $query->select(['id', 'first_name', 'last_name']);
            $query->where('verified', 'yes');
            $query->where('status', 'active');
            $query->where('role', '!=', 'admin');
            if ($q != '') {
                $query->whereRaw("CONCAT (first_name, ' ', last_name) like '%" . $q . "%'");
            }
            return $query;
        }])->where('status', '!=', 'paid')->groupBy('user_id')->get();

this is a relly specific example, but I thought it could illustrate my thoughts

[gcphost]

@ahmedash95 did you ever make a PR? Would like to see this missing feature.

[danielbaylis]

I don't believe this has become a feature. It would be important that it worked on all possible database drivers and I am not sure if they all support specifying an index by name?

[ahmedash95]

i'm agree with @danielbaylis , any idea about what should we do ( let it as a DB::row() solution or try to make a PR for that ) ?

[gcphost]

What about a beforeWhere()?

[harshsanghani]

I also want to user FORCE INDEX with laravel Eloquent so Please message here if you implement that.

[martianoff]

My solution:

Add following to the model class:

Class SomeModel extends Model {

    public static function IndexRaw($index_raw)
    {
        $model = new static();
        $model->setTable(\DB::raw($model->getTable() . ' ' . $index_raw));
        return $model;
    }

    ...
}

Now you can use simply something like this:

SomeModel::IndexRaw('FORCE INDEX (some_index_name)')->where('condition','=',true)->get();

[adelf]

In complex queries with with() previous solution doesn't work. Better to overwrite base query 'from' value instead:

    $query = SomeModel::where()->with()...->orderBy();

    $query->getQuery()->from(\DB::raw($query->getQuery()->from . ' FORCE INDEX (index_name)'));
    // or just
    $query->getQuery()->from(\DB::raw('`table` FORCE INDEX (index_name)'));

    $results = $query->get();

[thril]

I needed the FORCE INDEX for a join, so I used the following solution...

DB::table('table1 as t1')
    ->join(
        DB::raw('table2 as t2 FORCE INDEX (index_name)'),
        't1.some_field', '=', 't2.some_field'
    )
    ...