How Can I Add An Index To An Existing Table Columns In Laravel

admin_img Posted By Bajarangi soft , Posted On 23-09-2020

After a table has been created in a database, we find that it is advantageous to add an index to that table to speed up queries involving this table . To do this in SQL, we specify that we want to change the table structure via the ALTER TABLE command, followed by the ADD INDEX command to tell the RDBMS that we want to add an index .so in this article we are going to discuss how Add Index used in Laravel . Laravel schema builder supports several types of indexes

How Can I Add An Index To An Existing Table Columns In Laravel

Creating Indexes

The Laravel schema builder supports several types of indexes. The following example creates a new email column and specifies that its values should be unique. To create the index, we can chain the unique method onto the column definition:

$table->string('email')->unique();


Alternatively, you may create the index after defining the column. For example:

$table->unique('email');


You may even pass an array of columns to an index method to create a compound (or composite) index:

$table->index(['account_id', 'created_at']);


Laravel will automatically generate an index name based on the table, column names, and the index type, but you may pass a second argument to the method to specify the index name yourself:

$table->unique('email', 'unique_email');
 

Available Index Types

Each index method accepts an optional second argument to specify the name of the index. If omitted, the name will be derived from the names of the table and column(s) used for the index, as well as the index type.
 

Command Description
$table->primary('id'); Adds a primary key.
$table->primary(['id', 'parent_id']); Adds composite keys.
$table->unique('email'); Adds a unique index.
$table->index('state'); Adds a plain index.
$table->spatialIndex('location'); Adds a spatial index. (except SQLite)
 

Index Lengths & MySQL / MariaDB

Laravel uses the utf8mb4 character set by default, which includes support for storing "emojis" in the database. If you are running a version of MySQL older than the 5.7.7 release or MariaDB older than the 10.2.2 release, you may need to manually configure the default string length generated by migrations in order for MySQL to create indexes for them. You may configure this by calling the Schema::defaultStringLength method within your AppServiceProvider:
 

use Illuminate\Support\Facades\Schema;

/**
* Bootstrap any application services.
*
* @return void
*/
public function boot()
{
Schema::defaultStringLength(191);
}

Alternatively, you may enable the innodb_large_prefix option for your database. Refer to your database's documentation for instructions on how to properly enable this option.

Example(1)

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

Related Post