Adding an index in a Laravel migration is a one-liner. Choosing the right type of index is where it gets more interesting, and where MySQL can quietly ignore what you asked for.
Adding an index in a Laravel migration
To add an index to an existing table, use Schema::table and call index() with the column name:
public function up(): void
{
Schema::table('users', function (Blueprint $table) {
$table->index('external_id');
});
}
public function down(): void
{
Schema::table('users', function (Blueprint $table) {
$table->dropIndex(['external_id']);
});
}
Laravel names the index for you using the pattern {table}_{columns}_index, so this creates users_external_id_index.
Passing the column in an array to dropIndex() lets Laravel work out that name again when rolling back.
If you're creating the column at the same time, you can chain index() onto the column definition instead:
$table->string('external_id')->index();
A few variations you'll use regularly:
// Composite index across two columns (column order matters)
$table->index(['account_id', 'created_at']);
// Unique index
$table->unique('email');
// Custom index name
$table->index('external_id', 'users_external_id_idx');
That covers most cases, and every one of them creates the default index type. On MySQL that's a b-tree.
When should I use the default b-tree type of index?
MySQL supports several different types of index, each with their own advantages and disadvantages. The two you'll come across most are b-tree and hash.
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 LIKE comparisons that don't start with a wildcard.
Real world examples:
- Timestamps where you often need to perform range comparisons
- Text fields where you need to perform prefix matches (
LIKE 'abc%') - 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 <=>. It can't help
with ranges or sorting, but for straight lookups it can be very fast.
If you need to match to an exact value, for example in a key-value store, then a hash index is worth considering.
Real world examples:
- Key-value lookups
- IDs for entities on external systems, where you only ever look up an exact value
There's an 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?
It isn't covered in the Laravel documentation (at the
time of writing), but index() accepts the algorithm as its third argument. It's 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: illuminate/database Schema/Blueprint.php
Using the $algorithm parameter in your own migrations looks like this:
public function up(): void
{
Schema::table('users', function (Blueprint $table) {
$table->index('external_id', 'external_id_idx', 'hash');
});
}
On MySQL, Laravel adds using hash to the generated alter table ... add index statement.
The catch: InnoDB doesn't support hash indexes
When I first wrote this post I missed something important. If your table uses InnoDB, which is MySQL's default storage engine and almost certainly what your Laravel app is using, the hash type is ignored.
MySQL's index types per storage engine table lists BTREE as the only permissible type for InnoDB (and MyISAM). Explicit hash indexes are supported by the MEMORY and NDB engines. When you ask InnoDB for a hash index, MySQL doesn't fail. It uses the engine's default instead, so the migration runs cleanly and you end up with a b-tree.
MySQL 8.0 does at least record a note about it (code 3502, "storage engine default was used instead"), but you'd
only see it by running SHOW WARNINGS straight after the statement, and php artisan migrate doesn't show it.
You can check which type you actually got:
SELECT INDEX_NAME, INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users';
SHOW INDEX FROM users gives the same answer in its Index_type column. On an InnoDB table it will say BTREE,
whatever you passed to the migration.
Don't rely on SHOW CREATE TABLE for this. On older MySQL versions it echoes back the USING HASH you asked
for, while SHOW INDEX for the same index reports BTREE. It's a long-standing quirk, reported against MySQL 5.1
and never fixed (bug #40710,
bug #22632): MySQL remembers the requested type in case you later move the
table to an engine that supports it. That's how you can describe a table, see HASH, and still have a b-tree. When I
tested on MySQL 8.0.36, SHOW CREATE TABLE no longer included the clause, so at least it stops claiming a hash index.
The same applies on AWS. Aurora MySQL only supports InnoDB tables,
so a hash index there is always a b-tree, and RDS for MySQL behaves like the MySQL version it runs. If you're on
Aurora MySQL version 2 (MySQL 5.7 compatible), expect SHOW CREATE TABLE to show the USING HASH you asked for.
InnoDB does have an adaptive hash index, but
you don't create it per column. When it's switched on, InnoDB watches index searches and builds hash indexes in memory
for the ones that would benefit. It's controlled server-wide with the innodb_adaptive_hash_index setting, and the
default varies between MySQL versions, so check yours.
So in practice:
- InnoDB tables (most Laravel apps): use a normal
index(). The b-tree handles exact matches well, and passing'hash'changes nothing. - MEMORY or NDB tables: the
'hash'argument does what it says, and is worth using for equality-only lookups.
Building with Laravel? Check out EnvGrid
EnvGrid - Preview environments for Laravel: a live preview for every pull request, database migrated