The Role of Indexes
When you want to quickly find the data you need, you can use indexes.
With indexes, the database can directly locate the required data during retrieval, saving time—much like the table of contents at the front of a book, which helps you quickly find what you want to read.
If an index contains multiple columns, it follows the leftmost prefix rule, meaning it is used from left to right, starting with the leftmost column.
Types of Indexes
1. Unique Index
A unique index can be added to certain columns (such as an ID number) to ensure that no two records have the same value. By using the UNIQUE keyword, we can create a unique index.
1 | ADD UNIQUE INDEX uni_sf (sf); |
You can also add a unique constraint to a column without explicitly creating a unique index. In this case, the column is still required to have unique values.
1 | ADD CONSTRAINT uni_name UNIQUE (name); |
2. Primary Key Index
Once a primary key is created, an index is automatically created for it. This is why searching data by the primary key is faster.
A table can have only one primary key, but a primary key can consist of multiple columns, which is known as a composite (or joint) primary key.
1 | ALTER TABLE table_name ADD PRIMARY KEY (column); |
3. Normal (Non-unique) Index
Any column can have an index created on it. It is recommended to add indexes only to columns that are frequently queried. Avoid adding indexes to every column, as this can negatively impact performance.
1 | ALTER TABLE table_name ADD INDEX index_name (column); |