How to add a hash index to a column in a Laravel migration

MySQL supports several different types of index, each with their own advantages and disadvantages. The most common types are b-tree and hash.

When should I used the default b-tree type of index?

The default b-tree index type is useful for range comparisons, exact matches, and sorting. It's the most flexible type of index and can also help with queries that use LIKE and <>.

Real world examples:

• Timestamps where you often need to perform range comparisons
• Text fields where you need to perform partial matches (LIKE comparisons)
• IDs where you need to perform range comparisons

When should I use the hash index type?

The hash index is only useful for exact matches; "equality comparisons", such as =, IN, and <=>. But what it lacks in flexibility, it more than makes up for in speed.

If you need to match to an exact value, for example in a key-value store, then a hash index is the way to go.

Real world examples:

• Key-value lookups
• Foreign keys (where you do not need to perform range comparisons)
• IDs for entities on external systems

You can access a in-depth comparison of the two types of index in the MySQL documentation.

How do I specify the type of index in a Laravel migration?

Whilst it is missing from the Laravel documentation (at the time of writing), it is possible to add a hash index to a column in a Laravel migration.

Infact, the option to specify the algorithm is included in the Laravel source code:

/**
 * Specify an index for the table.
 *
 * @param  string|array  $columns
 * @param  string|null  $name
 * @param  string|null  $algorithm
 * @return \Illuminate\Database\Schema\IndexDefinition
 */
public function index($columns, $name = null, $algorithm = null)
{
    return $this->indexCommand('index', $columns, $name, $algorithm);
}

Source: https://github.com/illuminate/database/blob/10.x/Schema/Blueprint.php#L571-L582

Using the $algorithm parameter in your own migrations is as simple as:

public function up()
{
    Schema::table('users', function (Blueprint $table) {
        $table->index('external_id', 'external_id_idx', 'hash');
    });
}

Simple as that! You can now use the hash index type in your Laravel migrations.

Use MySQL? Check out BentoDB

BentoDB - Create a MySQL database with a single API call

Did you find this interesting?

Follow @jonmilsom