MySQL Indexes

-- Create a regular index on a single column
CREATE INDEX idx_customers_email ON customers (email);

-- Create a UNIQUE index (also enforces uniqueness)
CREATE UNIQUE INDEX uq_customers_email ON customers (email);

-- Create an index inline during CREATE TABLE
CREATE TABLE orders (
    order_id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id INT UNSIGNED NOT NULL,
    status      VARCHAR(20)  NOT NULL DEFAULT 'pending',
    ordered_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id),
    INDEX idx_orders_customer (customer_id),       -- index for FK lookups
    INDEX idx_orders_status   (status),            -- index for status filtering
    INDEX idx_orders_date     (ordered_at)         -- index for date range queries
) ENGINE=InnoDB;

-- Show all indexes on a table
SHOW INDEX FROM customers;
SHOW INDEX FROM orders;

-- Composite index: index on multiple columns
-- Useful when queries filter on both columns together
-- Column order matters: put the most selective column first
CREATE INDEX idx_products_category_price ON products (category, price);

-- This index helps queries like:
SELECT * FROM products WHERE category = 'Electronics' AND price < 100;
SELECT * FROM products WHERE category = 'Electronics' ORDER BY price;

-- But NOT queries that only filter on price (leftmost prefix rule)
-- SELECT * FROM products WHERE price < 100;  -- won't use the composite index

-- FULLTEXT index: for natural-language text search
ALTER TABLE products ADD FULLTEXT INDEX ft_products_name_desc (name, description);

-- Use FULLTEXT index with MATCH ... AGAINST
SELECT name, description,
       MATCH(name, description) AGAINST ('wireless mouse' IN NATURAL LANGUAGE MODE) AS relevance
FROM products
WHERE MATCH(name, description) AGAINST ('wireless mouse' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

-- Boolean mode FULLTEXT search
SELECT name FROM products
WHERE MATCH(name, description) AGAINST ('+wireless -cable' IN BOOLEAN MODE);

-- EXPLAIN: show query execution plan
EXPLAIN SELECT * FROM customers WHERE email = 'alice@example.com';
-- Look for: type=ref or type=const (good), type=ALL (full scan, bad)
-- key column shows which index was used

-- EXPLAIN with JOIN
EXPLAIN
SELECT c.first_name, o.order_id, o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'delivered';

-- EXPLAIN FORMAT=JSON for detailed output (MySQL 5.7+)
EXPLAIN FORMAT=JSON
SELECT * FROM products WHERE category = 'Electronics' AND price < 100\G

-- Drop an index
DROP INDEX idx_customers_email ON customers;

-- Drop index using ALTER TABLE
ALTER TABLE customers DROP INDEX idx_customers_email;

-- Covering index: index includes all columns needed by the query
-- MySQL can satisfy the query entirely from the index (no table lookup)
CREATE INDEX idx_orders_covering ON orders (customer_id, status, total, ordered_at);

-- This query is fully covered by the index above:
SELECT customer_id, status, total, ordered_at
FROM orders
WHERE customer_id = 1 AND status = 'delivered';

-- Index on foreign key columns (always index FK columns for JOIN performance)
CREATE INDEX idx_order_items_order   ON order_items (order_id);
CREATE INDEX idx_order_items_product ON order_items (product_id);

-- Avoid indexing low-cardinality columns (e.g. boolean flags)
-- An index on active (0 or 1) is rarely useful — MySQL may ignore it

-- Use FORCE INDEX hint to override the optimizer (use sparingly)
SELECT * FROM orders FORCE INDEX (idx_orders_date)
WHERE ordered_at > '2024-01-01';