Level 70
I think so. You may check this discussion- https://laracasts.com/discuss/channels/laravel/migrations-how-can-one-add-an-index-to-an-existing-column?page=1
Feb 28, 2017
6
Level 6
Does Eloquent use indexes?
So, this may seem like a rather silly question. But I'm a bit new to "advanced" database concepts.
I've read that, with MySQL/MariaDB, you can use the INDEX hint in your query to make use of your indexes.
EXPLAIN SELECT Name,CountryCode FROM City FORCE INDEX (Name)
WHERE name>="A" and CountryCode >="A";
So my questions are:
Does Eloquent use index hinting automatically?
Will MySQL/MariaDB not use indexes if you don't hint them?
Level 6
I know you can add indexes in Laravel's schema builder and all.
What's got my confused is the MySQL/MariaDB 'FORCE INDEX' statement. I'm just not sure if you need to hint/force an index in order for it to be used, or if the database will automatically pick up on it and use them on its own.
Level 2
The query engine of a database will decide what indices to use. Hints are onlyvto be used when you have performance issues you want to fix by overruling standard query optimisation.
1 like
Level 1
The solution here works flawlessly: https://github.com/laravel/internals/issues/33#issuecomment-245178924
Pasting here:
Class SomeModel extends Model {
public static function IndexRaw($index_raw)
{
$model = new static();
$model->setTable(\DB::raw($model->getTable() . ' ' . $index_raw));
return $model;
}
...
}
Usage:
SomeModel::IndexRaw('FORCE INDEX (some_index_name)')->where('condition','=',true)->get();
Level 1
The simplest way to implement it is install this package:
github: vpominchuk/laravel-mysql-use-index-scope
or just run: composer require vpominchuk/laravel-mysql-use-index-scope
The package adds useIndex($indexName) and forceIndex($indexName) functions to your model.
Usage:
$builder = MyModel::where('name', $name)->where('age', $age)->
useIndex($indexName)->...
Level 4
For Query Builder you can simply use:
$query= DB::table(DB::raw('table_name force index (index_name)'))
You can also override it using:
$query->fromRaw('table_name force index (another_index_name)')
which will be useful inside conditional statements
Please or to participate in this conversation.