What are the differences between PRIMARY, UNIQUE, INDEX and FULLTEXT in MySQL?

MySQL supports several types of indexes—structures that can significantly speed up data retrieval. Understanding how each type works is crucial for performance tuning and schema design. Below are the main types: PRIMARY, UNIQUE, INDEX (also referred to as a “normal” or “non-unique” index), and FULLTEXT, along with explanations of how and when to use each.

1. PRIMARY KEY

Definition

• A PRIMARY KEY is a combination of a unique constraint and an index.
• Each table can have only one primary key.

Key Characteristics

1. Uniqueness: No two rows can share the same primary key value.
2. Not NULL: A primary key column or set of columns cannot contain NULL values.
3. Data Integrity: Often used to uniquely identify each row in the table, ensuring data consistency.

Typical Usage

• Identifying rows in a table (e.g., id column).
• Ensuring each record is uniquely referenced (e.g., foreign keys in other tables reference the primary key).

Example

CREATE TABLE users (
  user_id INT AUTO_INCREMENT,
  username VARCHAR(50),
  PRIMARY KEY (user_id)
);

2. UNIQUE Index

Definition

• A UNIQUE index ensures that all values in the indexed columns are distinct.
• You can have multiple UNIQUE indexes per table.

Key Characteristics

1. Allows One NULL: In MySQL, a UNIQUE index can still allow one NULL value (or more, depending on the storage engine and version).
2. Prevents Duplicate Entries: Trying to insert a duplicate value into a unique column set will trigger an error.

Typical Usage

• Columns that must be unique within a table but are not the primary key (e.g., email addresses, phone numbers).

Example

CREATE TABLE employees (
  emp_id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100),
  UNIQUE (email)
);

3. INDEX (Non-Unique Index)

Definition

• A standard INDEX (often referred to as a “normal index”) does not enforce uniqueness.
• Primarily used to speed up data lookups.

Key Characteristics

1. Performance Boost: Indexing on frequently searched columns can dramatically reduce query time.
2. Allows Duplicates: No restriction on repeating values.
3. No NULL Restriction: Supports NULL without limitation.

Typical Usage

• Columns frequently used in WHERE clauses or JOIN conditions, but where uniqueness is not required.

Example

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  product_id INT,
  INDEX (customer_id)
);

Here, the index on customer_id improves queries like:

SELECT * FROM orders WHERE customer_id = 123;

4. FULLTEXT Index

Definition

• A FULLTEXT index is specialized for full-text searching of text-based columns (e.g., CHAR, VARCHAR, TEXT types).

Key Characteristics

1. Natural Language and Boolean Searches: FULLTEXT indexes enable more advanced text matching, like searching for words or phrases across large textual data.
2. MyISAM / InnoDB: Initially supported by the MyISAM engine, now InnoDB also supports FULLTEXT indexes (MySQL 5.6+).
3. Language-Specific Stopwords: MySQL can use stopwords (e.g., common words like “the”, “an”) which are ignored during searches.

Typical Usage

• Searching large text columns (e.g., product descriptions, blog posts, article bodies).

Example

CREATE TABLE articles (
  article_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255),
  content TEXT,
  FULLTEXT (title, content)
);

-- Example query using MATCH...AGAINST
SELECT * 
FROM articles
WHERE MATCH(title, content) 
      AGAINST('database indexing' IN NATURAL LANGUAGE MODE);

Summary

Best Practices

1. Use Primary Key for Identification
   Keep the PRIMARY KEY simple (often a numeric AUTO_INCREMENT column).
2. Leverage UNIQUE
   Use UNIQUE constraints on columns that shouldn’t contain duplicate values (e.g., emails, usernames).
3. Index Strategically
   Avoid over-indexing; each additional index can slow down INSERT/UPDATE operations. Focus on columns frequently used in WHERE or JOIN.
4. Use FULLTEXT for Search
   Ideal for text-based searches on columns with paragraphs or large textual data.
5. Monitor Performance
   Use EXPLAIN on your queries to see if the correct index is being used.

Level Up Your SQL and Database Skills

For a deeper dive into SQL queries, database design, and optimization, check out these courses from DesignGurus.io:

Grokking SQL for Tech Interviews

• Learn advanced querying techniques, performance tuning, and real-world problem-solving patterns.

Grokking Database Fundamentals for Tech Interviews

• Gain insights into normalization, indexing strategies, transaction management, and more for scalable systems.

Practice with Mock Interviews

If you’re preparing for technical interviews where database expertise matters, consider scheduling Mock Interviews with ex-FAANG engineers at DesignGurus.io. You’ll receive personalized feedback on your SQL approach, communication style, and overall problem-solving strategy—helping you stand out in a competitive job market.

In conclusion, different index types in MySQL cater to different needs:

• PRIMARY ensures uniqueness at the table level and acts as the main row identifier.
• UNIQUE enforces uniqueness on a column or set of columns.
• INDEX improves query performance without enforcing uniqueness.
• FULLTEXT specializes in text searching.

Each plays a pivotal role in optimizing data storage, retrieval, and maintaining data integrity. By selecting and combining them wisely—and continuously monitoring performance—you’ll build more efficient, scalable MySQL databases.