How To Create Composite Indexes With Migration In Laravel
![](https://d33wubrfki0l68.cloudfront.net/9a0c6f1a4299a5bb485a8189b16eb1d2c94e1323/0424a/create-composite-indexes-with-migration-in-laravel/featured_huac90646a4b03996012122b8449664c74_6814_720x2500_fit_q75_h2_lanczos_3.webp)
The Schema Builder in Laravel supports different types of indexes.
In this tutorial we’ll see how to create composite or compound index using index
method with migration
.
Tested with Laravel version 5.8, 6 and 7.
Kindly check for your version on the official DOC for migrations. The DOC link will take you to Laravel 6.
Syntax
$table->index(['column1', 'column2']);
- For creating composite key, we need to pass
array
type of parameter toindex
method. - We’ve specified our both columns on which we want to apply compound index.
Example
- In this example we’re assuming that we already have
users
table withemail
andphone_number
fields. And we want to apply composite index on it. - We’ve prepared
AlterUsersTable
migration with below code
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class AlterUsersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up(): void
{
Schema::table('users', function (Blueprint $table) {
$table->index(['email', 'phone_number']);
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down(): void
{
Schema::table($this->tableName, function (Blueprint $table) {
/*
* Body to reverse, the up functionality
* The composite index needs to be dropped here
*/
});
}
}
After executing php artisan migrate
, Laravel will update the migration and will take care of generating compound index.
Laravel will implicitly create compound/composite key with name users_email_phone_number_index
, based on table name and our specified fields.
![Table structure screenshot from phpMyAdmin with compound keys](https://d33wubrfki0l68.cloudfront.net/0b33042e5f2fc16b0c9478738ce014b694217fb8/287ee/create-composite-indexes-with-migration-in-laravel/composite-index-migration_hu0f152ed4dd83159d849142125f846049_48160_93163abead28f07897b70e3fbc7ea15e.webp)
Note
- Laravel will automatically create and name our index key based table and our specified fields. We’re not explicitly specifying index name here.
- In order to keep less confusion,
down
function has been kept empty, so this composite index can be dropped in it.
Happy coding