Index ​

In relational databases, when dealing with thousands or even millions of records, utilizing indexes is essential for achieving fast query speeds.

An index is a data structure that pre-sorts the values of one or more columns. By using indexes, the database system can locate matching records directly without scanning the entire table, significantly speeding up query performance.

For example, consider the students table:

If queries are frequently based on the score column, an index can be created for it:

ALTER TABLE students
ADD INDEX idx_score (score);

This creates an index named idx_score on the score column. The index name is arbitrary, and if you want to create a multi-column index, you can list the columns in parentheses:

ALTER TABLE students
ADD INDEX idx_name_score (name, score);

Index Efficiency ​

The efficiency of an index depends on how distinct the values in the indexed column are. The more varied the values, the higher the index efficiency. Conversely, if a column has many identical values (e.g., the gender column where half are M and half are F), creating an index on that column may not be beneficial.

You can create multiple indexes on a table. The advantages of indexes include improved query performance, while the downsides are that they can slow down the speed of insert, update, and delete operations since the indexes need to be modified accordingly. Therefore, the more indexes there are, the slower these operations become.

Primary Key Index ​

Relational databases automatically create a primary key index for primary keys. Using a primary key index is the most efficient, as primary keys guarantee absolute uniqueness.

Unique Index ​

In designing relational tables, columns that appear to be unique (like ID numbers or email addresses) should not be used as primary keys due to their business implications. However, these columns may require uniqueness constraints, ensuring that no two records can have the same value.

For instance, if we want to ensure that name in the students table is unique, we can add a unique index:

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

Alternatively, we can enforce uniqueness without creating an index:

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

In this case, the name column maintains uniqueness without an index.

User Transparency ​

For users and applications, the existence of database indexes is transparent. When querying the database, if an index is available, the system will use it automatically to enhance performance. If no index exists, the query will still execute normally but may run slower. Thus, indexes can be incrementally optimized during database usage.

Summary ​

• Creating indexes on database tables can improve query speeds.
• Unique indexes can ensure that a column's values are unique.
• Database indexes are transparent to users and applications.