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

When I started working on a medium-sized MySQL project with hundreds of thousands of rows of data, I found that query times gradually increased from milliseconds to seconds. It was then that I began to delve into indexes. Over time, I realized that understanding MySQL indexes is less about theory and more about practical debugging and observing real-world query performance. Below, I will share how I used indexes in a real-world project.

1. Indexes in MySQL

I like to think of indexes as MySQL’s GPS. Without an index, MySQL has to search for matches along every street (every row). With an index, it can jump directly to the target location.

Of course, indexes don’t come without a cost. I’ve noticed that when we add indexes to frequently updated tables, the speed of INSERT and UPDATE operations decreases slightly. But the speed improvement for SELECT queries is usually worthwhile.

2. Primary Key Index

Every table I’ve designed has a primary key. It’s like the table’s ID card. In a previous project, there was an “Employee” table that consistently encountered duplicate entries when merging imported CSV data because it lacked a primary key. Defining a primary key immediately solved the problem.

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

Here’s what I observed:

• employee_id automatically got a unique B-Tree index.
• Trying SELECT * FROM employees WHERE employee_id = 101; was almost instantaneous, even with 500,000 rows.

I remember running the same query before adding the primary key—it took almost half a second. After creating the index, the time plummeted to 0.002 seconds. That’s when I truly appreciated the value of indexes.

3. Unique Index

When working with the users table, a unique index was a lifesaver. We needed to ensure that no two users would have the same email address. Initially, I tried to handle this in the application logic, but concurrent insert operations caused this approach to fail completely—duplicate records would still get mixed in.

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

A few things I learned:

• Attempting to insert the same email twice threw the classic:
  ERROR 1062: Duplicate entry '[email protected]' for key 'idx_email'
• Unlike primary keys, a table can have multiple unique indexes. On this project, we had one for email and one for username.

I usually combine application-layer checks to achieve a smoother user experience, but database enforcement is my security safeguard.

4. Normal (Regular) Index

I have a love-hate relationship with regular indexes. In our employees table, we frequently filter by last_name. Initially, queries like SELECT * FROM employees WHERE last_name = 'Smith'; were unbearably slow. After adding a regular index, the speed improvement was significant.

CREATE INDEX idx_last_name ON employees(last_name);

Some observations:

• Queries that once took 0.5–1 second dropped to 0.003–0.005 seconds.
• Duplicate last names were fine—no restrictions here.
• The downside? Every INSERT or UPDATE on last_name got slightly heavier, but in our case, reads far outweighed writes.

I usually use the EXPLAIN command to test whether MySQL is actually using the index. Sometimes, MySQL will ignore the index if it thinks a full table scan is more efficient, which initially surprised me.

5. Full-Text Index

Full-text search was a game-changer for a blog project. Initially, I tried using LIKE '%keyword%', which choked the database as articles grew. Switching to a full-text index made searches snappy.

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

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

• Before the full-text index, a search across 50,000 articles took 6–8 seconds.
• After indexing, the same search was under 0.2 seconds.

Lesson learned: full-text indexes are not magical—they only work on CHAR, VARCHAR, or TEXT columns. Trying them on non-text fields gives errors or just doesn’t help.

6. Key Differences Between Index Types

I often keep a mental cheat sheet:

When planning a table, I ask myself: “Which queries are critical, and which data must be unique?” That usually guides the indexing strategy.

7. Best Practices I Follow

Over the years, I’ve summarized some practical rules:

• Always define a primary key. This is just as important as maintaining a database table.
• Use unique indexes on email, username, or other critical fields. In concurrent environments, application checks alone are insufficient.
• Add regular indexes to frequently used columns. But don’t overuse them—too many indexes will slow down write operations.
• Use full-text indexes only for large text fields. Trying to use full-text indexes on small lookup tables is overkill.

Once, I created 10 indexes for a table, thinking “the more the better.” The result was a drastic drop in insert speed. Performance only returned to acceptable levels after deleting 6 of the indexes.

In short, indexing isn’t just about speed – it’s about how applications read and write data. My advice: experiment, measure, tweak, and always check the output of EXPLAIN. This is how you turn theory into real performance improvements.