MySQL Database Index Concepts You Probably Don’t Fully Understand

Some important concepts behind indexing

Jerry An
Level Up Coding
Published in
4 min readMay 23, 2021

--

DATABASE Indexing

Indexes are used to find rows with specific values quickly.

If a table has an index for the column in question, MySQL can quickly determine the position to seek in the middle of the data file without having to look at all the data. It is much faster than reading every row sequentially.

In this post, we’ll look at some of the important concepts behind indexing.

Example

Let’s first create a MySQL table.

CREATE TABLE Customer( 
`custId` INT,
`name` VARCHAR(50),
`mobileNo` VARCHAR(11)
);
ALTER TABLE `Customer` ADD PRIMARY KEY (`custId`);ALTER TABLE `mobileNo` ADD UNIQUE INDEX (`MobileNo`);

Assume we have a Customer table, it has three fields: custId, name and mobileNo. The CustId is the table’s primary key and mobileNo is the unique secondary index.

Some Data records are shown in the following table.

+--------+--------+---------+
| CustId | Name | PhoneNo |
+--------+--------+---------+
| 1 | Jerry | 111 |
| 2 | Moly | 112 |
| 3 | Tom | 108 |
| 4 | Tony | 107 |
| 5 | Roc | 101 |
| 6 | Moly | 103 |
| 7 | Sunny | 102 |
| 8 | Jack | 110 |
| 9 | Lily | 108 |
| 10 | Emily | 105 |
| 11 | Lee | 104 |
| 12 | Will | 113 |
| 13 | Chen | 109 |
| 14 | An | 106 |
+--------+--------+---------+

Primary Key 🔑

A primary key is a key in a relational database that uniquely specifies a record, such as a driver's license number, telephone number, or vehicle identification number (VIN).

It has a bunch of attributes:

  • One and only one primary key per table
  • Unique for each row
  • Not null

One Clustered Index

--

--