How to Create Table Index in Laravel

Introduction

This article will demonstrate how to create table indexes in Laravel. Indexes are used to maintain unique constraints and also to improve application performances.

Create the Migration with Artisan Make Command

Firstly run the artisan migration generation script to create the migration php file.

php artisan make:migration create_index

Now let's assume that we already have a table named users_agents with the following fields - id (primary key), user_id, and agent_id.

Note that primary keys of a table is automatically indexed. Or rather, primary key is a special kind of index.

Create Index in Migration PHP Class

To create an index with the user_id and agen_id fields:

Schema::table('user_agent', function (Blueprint $table) {
	$table->index(['user_id', 'agent_id']);
});

If you'd like to give the index a name:

$table->index(['user_id', 'agent_id'], 'useragentindex');

Lastly, apply the migration:

$ php artisan migrate
Migrating: 2021_08_18_132013_create_index
Migrated:  2021_08_18_132013_create_index (2.96 seconds)

The index creation code should be placed inside the up() function and code to drop index should be included in the down() function of the migration class.

class CreateIndex extends Migration
{
    public function up()
    {
        Schema::table('user_agent', function (Blueprint $table) {
            $table->index(['user_id', 'agent_id'], 'useragentindex');
        });
        
        Schema::table('accounts', function (Blueprint $table) {
            $table->index('user_id', 'accountuseridindex');
        });
    }

    public function down()
    {
        Schema::table('user_agent', function (Blueprint $table) {
            $table->dropIndex('useragentindex');
        });
            
        Schema::table('accounts', function (Blueprint $table) {
            $table->dropIndex('accountuseridindex');
        });
    }
}
CreateIndex.php
Tip: You can create multiple indexes in the migration class by adding multiple Schema::table methods.

View Index details in MySQL database

In MySQL, we can view the index details of a table with the show index command:

show index from user_agent;
show index from accounts;

Conclusion

In this article, we covered how to create indexes with Laravel Migrations. We also saw how to apply migrations and roll back them.

If you liked the way we presented the article let me know and if you have any suggestions to improve feel free to share them with us.

Asha

Asha

I am a self mentored web enthusiast who likes to inspect element everything I see on the web.