KOSUNI KOREA
Korean News in English
Technology

Understanding PRIMARY KEY, AUTO_INCREMENT, and INDEX in MySQL: A Practical Guide

Published October 17, 2026 · 1 views
Understanding PRIMARY KEY, AUTO_INCREMENT, and INDEX in MySQL: A Practical Guide


[Mysql 강좌] 9강 PRIMARY KEY, AUTO_INCREMENT, INDEX 이해하기


Good databases are designed from the start

So far, we've learned how to:

  • Create a database

  • Create tables

  • Add data

  • Query data

  • Edit data

  • Delete data

Now it's time to learn some more practical database design concepts.

This lesson covers three features used in nearly every table of real-world projects:

  • PRIMARY KEY

  • AUTO_INCREMENT

  • INDEX


What is a PRIMARY KEY?

In Korean, this is called primary key.

A primary key is a unique identifier for each piece of data.

For example, consider a member table:

ID

Name

Email

1

Hong Gil-dong

hong@test.com

2

Kim Cheol-su

kim@test.com

3

Lee Young-hee

lee@test.com

In this case, ID is the PRIMARY KEY.

While names can be duplicated,

ID cannot be repeated under any circumstances.


Why use a PRIMARY KEY?

For example, consider two members with the same name:

ID

Name

1

Hong Gil-dong

2

Hong Gil-dong

You wouldn't know which member to update based solely on name.

However,

ID = 2

would allow you to precisely identify one person.

This is why most tables use a PRIMARY KEY.


Creating a PRIMARY KEY

You can specify a PRIMARY KEY when creating a table:

CREATE TABLE member (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

Now ID serves as the primary key.

Duplicate IDs cannot be stored.


PRIMARY KEY characteristics

A PRIMARY KEY has these features:

  • No duplicates allowed

  • Cannot contain NULL values

  • Only one per table

  • Enables faster data retrieval

It's commonly used for member IDs, post numbers, comment IDs, and more.


What is AUTO_INCREMENT?

Manually entering member numbers would be tedious.

For example, managing numbers like:

1

2

3

4

directly would lead to errors easily.

This is why MySQL includes an automatic numbering feature called AUTO_INCREMENT.


Using AUTO_INCREMENT

You can write it like this:

CREATE TABLE member (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

When adding members, you don't need to input the ID:

INSERT INTO member(name, email)
VALUES('Hong Gil-dong', 'hong@test.com');

The system will automatically assign numbers like:

1

2

3

4


Boards also use AUTO_INCREMENT

For forums, you don't manually enter numbers either.

CREATE TABLE board (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT
);

When saving posts, numbers are automatically generated like:

1st post

2nd post

3rd post


What is an INDEX?

An INDEX helps find data more quickly.

In simple terms, it's like a book's table of contents.

When looking for specific information in a book, you don't read from beginning to end—you check the table of contents first and go directly to the relevant page.

An INDEX works similarly.


Why use an INDEX?

If there are 10 members, finding someone is quick.

But what about:

100,000 members

1 million members

10 million members

Searching through all data to find one name would be extremely slow.

This is why INDEXes are used.


Creating an INDEX

If email searches happen frequently, you can create an index like this:

CREATE INDEX idx_email
ON member(email);

This significantly speeds up email searches.


PRIMARY KEY is also an INDEX

A common point of confusion for beginners.

A PRIMARY KEY automatically creates an index.

In other words,

PRIMARY KEY

↓

Automatic index creation

This happens. That's why searching by PRIMARY KEY is very fast.


When to use INDEX?

Consider creating indexes for columns that are frequently searched, such as:

It's best to create indexes only for columns that are frequently searched.


What happens if you create too many INDEXes?

Making too many indexes isn't beneficial.

The more indexes you have, the slower:

operations can become.

Therefore,

Create indexes only for columns that are frequently searched.


Combining PRIMARY KEY + AUTO_INCREMENT

In real-world development, this is the standard pattern:

id INT AUTO_INCREMENT PRIMARY KEY

This structure appears in member tables,

board tables,

comment tables,

category tables,

and nearly all other tables.

This is the most common pattern used in professional development.


Board Design Example

CREATE TABLE board (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    writer VARCHAR(50),
    content TEXT,
    created_at DATETIME
);

This structure alone completes the basic design for a forum.


Common beginner mistakes

1. Forgetting to create a PRIMARY KEY

Without a primary key, managing data becomes extremely difficult.

Get into the habit of creating a PRIMARY KEY for every table.


2. Not using AUTO_INCREMENT

Manually entering numbers increases the risk of duplicates and errors.

Using automatic numbering is always safer.


3. Creating indexes on every column

While indexes improve search speed,

creating too many isn't beneficial.


4. Confusing PRIMARY KEY with INDEX

A PRIMARY KEY is a type of index, but not all indexes are PRIMARY KEYS.

Remember this distinction.


Key takeaways from this lesson

Important points to remember:

These three concepts are fundamental to database design and must be understood before moving forward.


In conclusion

This lesson covered PRIMARY KEY, AUTO_INCREMENT, and INDEX—essential concepts in database design.

You now have the foundational skills to properly design a single table.

Next, we'll learn how to connect multiple tables using JOINs, helping you understand real-world database structures used in services.

FAQ

What is the difference between a PRIMARY KEY and an INDEX?

A PRIMARY KEY automatically creates an index, but not all indexes are PRIMARY KEYS. A PRIMARY KEY must be unique and non-null, while other indexes can allow duplicates.

Why should I use AUTO_INCREMENT instead of manual numbering?

AUTO_INCREMENT prevents duplicate numbers and human errors by automatically generating sequential IDs when adding new records.

Korean Source

This article is an English localization of a Korean-language report from BlogScore. Original: [Mysql 강좌] 9강 PRIMARY KEY, AUTO_INCREMENT, INDEX 이해하기