Column types — string, text, integer, bigInteger, decimal, json, uuid, etc.
Concept
Indexes are the most impactful database performance optimization. An index is a data structure (usually a B-tree) that the database maintains alongside the table, enabling fast lookups without scanning all rows.
Blueprint index methods:
index(string|array $columns, string $name = null): Standard B-tree index.unique(string|array $columns, string $name = null): Unique constraint + index.primary(string|array $columns): Primary key.id()sets this automatically.fullText(string|array $columns, string $name = null): Full-text search index (MySQL).spatialIndex($column): Spatial index for geometry types.
Column-level shorthand: Chain index methods directly on column definitions: $table->string('email')->unique(). Equivalent to adding ->unique('email') separately.
Compound indexes: Pass an array of columns. Order matters: index(['status', 'created_at']) helps queries filtering on status and sorting by created_at. The leftmost column rule — the index helps queries using any prefix of the columns.
Dropping indexes:
dropIndex(string|array): By name or column array.dropUnique(string|array).dropPrimary().dropFullText(string|array).
Auto-generated index names: Laravel generates names like users_email_unique, orders_status_created_at_index. You can override with the second parameter.
When NOT to index: Indexes have write overhead (INSERT/UPDATE/DELETE are slower). Don't index low-cardinality columns (e.g., boolean columns with only 2 values) — the optimizer won't use them.
Code Example
<?php
Schema::create('orders', function (Blueprint $table) {
$table->id();
$table->foreignId('user_id')->constrained();
$table->string('status', 20)->default('pending');
$table->decimal('total', 10, 2);
$table->string('reference', 50)->unique(); // unique constraint + index
$table->text('notes')->nullable(); // text columns — don't index
$table->timestamps();
// Explicit indexes at end of table definition
$table->index('status'); // single-column
$table->index(['status', 'created_at']); // compound index
$table->index(['user_id', 'status', 'created_at']); // covers common query patterns
});
// In a separate migration (adding indexes to existing table)
Schema::table('orders', function (Blueprint $table) {
// Add compound index for a common query pattern: WHERE status = ? ORDER BY created_at DESC
$table->index(['status', 'created_at'], 'orders_status_date_idx');
// Full-text search index
$table->fullText(['title', 'body'], 'posts_search_idx');
});
// Dropping indexes
Schema::table('orders', function (Blueprint $table) {
$table->dropIndex(['status']); // by column (generates name)
$table->dropIndex('orders_status_date_idx'); // by name
$table->dropUnique(['reference']); // drop unique index
$table->dropFullText('posts_search_idx');
});
// Check index effectiveness with EXPLAIN
// EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-01-01';
// Look for: key = 'orders_status_created_at_index' (index used)
// Avoid: type = 'ALL' (full table scan)