MySQL Indexes

1. What is an Index?

• An index is like a book’s index: it helps MySQL find rows faster without scanning the whole table.
• Technically: it is a data structure (usually B-Tree) that improves query performance on selected columns.

👉 Without index → MySQL does full table scan (slow for large data).
👉 With index → MySQL can jump directly to matching rows (fast).

---

2. Creating an Index

-- Create index on a column
CREATE INDEX idx_name ON customers(name);

-- Create composite index (multiple columns)
CREATE INDEX idx_name_city ON customers(name, city);

---

3. Types of Indexes in MySQL

🔹 1. Primary Key Index

• Automatically created when you define a PRIMARY KEY.
• Ensures uniqueness + not null.

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

---

🔹 2. Unique Index

• Ensures all values in a column are unique.

CREATE UNIQUE INDEX idx_email ON customers(email);

---

🔹 3. Regular (Non-Unique) Index

• Speeds up queries but allows duplicates.

CREATE INDEX idx_city ON customers(city);

---

🔹 4. Composite Index

• Index on multiple columns.
• Order matters → (col1, col2) is different from (col2, col1).

CREATE INDEX idx_name_city ON customers(name, city);

👉 Useful for queries like:

SELECT * FROM customers WHERE name='John' AND city='Delhi';

---

🔹 5. Fulltext Index

• For text search (MATCH ... AGAINST).
• Used on CHAR, VARCHAR, TEXT columns.

CREATE FULLTEXT INDEX idx_content ON articles(content);

Query example:

SELECT * FROM articles
WHERE MATCH(content) AGAINST('database');

---

🔹 6. Spatial Index

• For geometric data types (POINT, LINE, POLYGON).
• Requires MyISAM or InnoDB with spatial features.

CREATE SPATIAL INDEX idx_location ON places(location);

---

4. Dropping an Index

DROP INDEX idx_city ON customers;

---

5. When to Use Indexes

✅ Use indexes when:

• Columns are used in WHERE, JOIN, ORDER BY, GROUP BY.
• Columns often searched/filtered.

❌ Avoid indexes when:

• Table is small (overhead > benefit).
• Column has low selectivity (e.g., gender = M/F).
• Frequent INSERT/UPDATE/DELETE (index must update each time → slows writes).

---

6. Checking Indexes

SHOW INDEXES FROM customers;

---

7. Example

-- Table
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  product VARCHAR(100),
  order_date DATE,
  INDEX idx_customer_date (customer_id, order_date)
);

-- Query that benefits from index
SELECT * FROM orders
WHERE customer_id = 5
ORDER BY order_date DESC;

👉 Here, idx_customer_date makes the query much faster.

---

✅ Summary:

• Index = performance booster for searches.
• Types: Primary, Unique, Regular, Composite, Fulltext, Spatial.
• Tradeoff = faster reads but slower writes + more storage.