Indexes dramatically improve query performance by allowing faster data retrieval. Essential for large databases.

Create Index

CREATE INDEX idx_email ON customers(email);

Create Unique Index

CREATE UNIQUE INDEX idx_username ON users(username);

Create Composite Index

CREATE INDEX idx_name_date ON orders(customer_name, order_date);

Drop Index

DROP INDEX idx_email ON customers;

When to Create Indexes

Columns in WHERE clauses

Columns in JOIN conditions

Columns in ORDER BY

Foreign key columns

When NOT to Index

Small tables

Frequently updated columns

Columns with low selectivity

View Indexes

SHOW INDEX FROM customers;

Optimize with AI2sql

AI2sql suggests indexes for better performance.