MySQL Index Types Explained: Primary Key, Unique, Normal, and Full-Text

1. What is an Index in MySQL?

An index is a database structure that improves the speed of data retrieval on a table. It acts like a roadmap, allowing MySQL to quickly locate rows without scanning the entire table. While indexes enhance SELECT queries, they require additional storage and can slightly slow down INSERT, UPDATE, and DELETE operations.

2. Primary Key Index

The Primary Key is a unique identifier for each row in a table. Creating a primary key automatically creates a unique index, ensuring each value in the key column is distinct and not null.

Example

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

Explanation:

• employee_id is the primary key.
• MySQL automatically creates a unique B-Tree index on employee_id.
• Guarantees each employee has a unique identifier.
• Example usage:

SELECT * FROM employees WHERE employee_id = 101;

• The query uses the primary key index to locate the row efficiently.

3. Unique Index

A Unique Index ensures that all values in a column or a combination of columns are distinct. Unlike a primary key, a table can have multiple unique indexes, and columns can accept NULL values (depending on the database engine).

Example

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(100),
    username VARCHAR(50),
    UNIQUE INDEX idx_email (email)
);

Explanation:

• idx_email enforces uniqueness on the email column.
• Prevents two users from having the same email address.
• Example query:

INSERT INTO users (user_id, email, username)
VALUES (1, '[email protected]', 'john');

• Attempting to insert another user with '[email protected]' will fail.

4. Normal (Regular) Index

A Normal Index or Non-Unique Index improves query performance without enforcing uniqueness. It is commonly used for columns frequently searched with WHERE, ORDER BY, or GROUP BY.

Example

CREATE INDEX idx_last_name ON employees(last_name);

Explanation:

• Creates an index on the last_name column.
• Speeds up queries filtering by last name:

SELECT * FROM employees WHERE last_name = 'Smith';

• MySQL uses the index to locate rows quickly.
• Unlike unique indexes, duplicates are allowed.

5. Full-Text Index

A Full-Text Index is designed for text searching in large text columns (CHAR, VARCHAR, or TEXT). It supports natural language searches using MATCH() and AGAINST().

Example

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(255),
    body TEXT,
    FULLTEXT INDEX idx_body (title, body)
);

Explanation:

• idx_body is a full-text index on title and body.
• Enables fast keyword searches in text fields.
• Example search:

SELECT article_id, title
FROM articles
WHERE MATCH(title, body) AGAINST('MySQL optimization');

• MySQL searches the indexed text efficiently rather than scanning all rows.
• Ideal for blogs, content management systems, and search engines.

6. Key Differences Between Index Types

7. Best Practices for Using Indexes

1. Use Primary Keys for table identity: Always define a primary key for relational integrity.
2. Use Unique Indexes for critical columns: Emails, usernames, or social security numbers benefit from uniqueness enforcement.
3. Add Normal Indexes on frequently searched columns: Improves query performance without uniqueness constraints.
4. Use Full-Text Indexes for large text searches: Avoid using normal indexes on large text fields for keyword searches.
5. Avoid over-indexing: Excessive indexes slow down write operations.