How to Add the Right Database Indexes

-- Without an index on email: scans all rows
SELECT id, name FROM users WHERE email = 'alice@example.com';
-- EXPLAIN → type: ALL, rows: 500000

-- After adding an index
CREATE INDEX idx_users_email ON users (email);

-- Same query
SELECT id, name FROM users WHERE email = 'alice@example.com';
-- EXPLAIN → type: ref, rows: 1

-- High selectivity = good index candidate
-- email: every value is unique → perfect
CREATE INDEX idx_users_email ON users (email);

-- slug: unique per record → perfect
CREATE UNIQUE INDEX idx_posts_slug ON posts (slug);

-- user_id on orders: one user has many orders → good
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Low selectivity = poor index candidate — avoid
-- is_published TINYINT(1): only 2 distinct values → bad
-- gender ENUM('m','f','other'): only 3 values → likely bad

-- Query: WHERE status = 'active' AND created_at > '2024-01-01' ORDER BY created_at DESC

-- Bad order: range before equality wastes the index
CREATE INDEX idx_bad ON orders (created_at, status);

-- Good order: equality first, range/sort second
CREATE INDEX idx_orders_status_created ON orders (status, created_at);
-- MySQL uses (status) to narrow to 'active' rows,
-- then (created_at) to scan in order — no filesort needed

-- Verify with EXPLAIN
EXPLAIN SELECT id FROM orders
WHERE status = 'active'
  AND created_at > '2024-01-01'
ORDER BY created_at DESC;

-- Query reads only id, status, created_at from orders
SELECT id, status, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC;

-- A covering index includes all referenced columns
CREATE INDEX idx_orders_covering
    ON orders (user_id, created_at, status, id);
--            ^^^^^^^ WHERE   ^^^^^^^^^^^ ORDER BY   ^^^^^^^^ SELECT

-- EXPLAIN will now show: Extra = Using index
-- No table lookup required — 100% served from the index

-- Single-column unique index
ALTER TABLE users ADD UNIQUE INDEX idx_users_email_unique (email);

-- Composite unique index (combination must be unique)
-- e.g. a user can only follow a given tag once
ALTER TABLE user_tag_follows
    ADD UNIQUE INDEX idx_user_tag_unique (user_id, tag_id);

-- In a Laravel migration
Schema::table('users', function (Blueprint $table) {
    $table->unique('email');
});

Schema::table('user_tag_follows', function (Blueprint $table) {
    $table->unique(['user_id', 'tag_id']);
});

-- Check for un-indexed foreign keys
SELECT
    kcu.TABLE_NAME,
    kcu.COLUMN_NAME,
    kcu.CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE kcu
LEFT JOIN information_schema.STATISTICS s
    ON  s.TABLE_SCHEMA = kcu.TABLE_SCHEMA
    AND s.TABLE_NAME   = kcu.TABLE_NAME
    AND s.COLUMN_NAME  = kcu.COLUMN_NAME
WHERE kcu.REFERENCED_TABLE_NAME IS NOT NULL
  AND kcu.TABLE_SCHEMA = 'your_database'
  AND s.INDEX_NAME IS NULL;
-- Returns FK columns that have no index — add one for each result

-- In a Laravel migration, this is always safe
Schema::table('orders', function (Blueprint $table) {
    $table->index('user_id');  // index the FK
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});

<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('orders', function (Blueprint $table) {
            // Single-column index
            $table->index('user_id');

            // Composite index (equality + range)
            $table->index(['status', 'created_at'], 'idx_orders_status_created');

            // Unique index
            $table->unique('reference_number');

            // Covering index — include additional columns
            $table->index(['user_id', 'created_at', 'status', 'id'], 'idx_orders_covering');
        });
    }

    public function down(): void
    {
        Schema::table('orders', function (Blueprint $table) {
            $table->dropIndex('idx_orders_status_created');
            $table->dropIndex('idx_orders_covering');
            $table->dropUnique(['reference_number']);
        });
    }
};

-- Find indexes never used in queries (MySQL 5.7+ with sys schema)
SELECT
    object_schema,
    object_name AS table_name,
    index_name
FROM sys.schema_unused_indexes
WHERE object_schema = 'your_database'
ORDER BY object_name;

-- Also check for duplicate / redundant indexes
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'your_database';
-- e.g. index on (a) is redundant if (a, b) already exists
-- because (a, b) satisfies all queries that (a) alone would

-- Drop an index
ALTER TABLE orders DROP INDEX idx_old_unused_index;
-- Or in Laravel
-- $table->dropIndex('idx_old_unused_index');