How To Create Composite Indexes With Migration In Laravel

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 to index 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 with email and phone_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.

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