How Does Database Indexing Improve Query Performance and When Should It Be Used?

Database Index: Working Principle and Application Scenarios

Database indexing is a technology that organizes data in a specific way to improve database query performance. As databases grow in size, indexing becomes critical because it significantly speeds data search and retrieval.

Importance of Index

When data is stored on disk, it is divided into data blocks. Accessing these data blocks requires a complete read of the entire block, which is a time-consuming process. Searching unsorted data requires a linear search, which involves scanning large blocks of data and is inefficient.

What is a database index?

Indices enable faster data searches by sorting data on specific fields. When an index is created on a field, it creates an additional data structure containing the field value and its corresponding record pointer. This index structure is sorted so that binary searches can be performed, significantly improving performance.

How indexes work

Consider the following sample database table:

字段名	数据类型	大小
id	无符号INT	4 字节
firstName	Char(50)	50 字节
lastName	Char(50)	50 字节
emailAddress	Char(100)	100 字节

Suppose we have a table with 5,000,000 records using the MyISAM engine (default block size is 1,024 bytes). The average number of data block accesses required to search for an ID value (sorted key field) is 20, which is well below the 500,000 accesses required for a linear search.

However, searching for an unsorted non-key field such as firstName requires scanning the entire table (1,000,000 block accesses). This is where indexes come into play.

The index of firstName will have the following structure:

字段名	数据类型	大小
firstName	Char(50)	50 字节
(记录指针)	特殊	4 字节

The index allows a binary search that requires an average of only 20 block accesses to retrieve the firstName value. This is a huge improvement over the 1,000,000 accesses required in an unindexed table.

When to use indexes

While indexing can improve query performance, it also comes with overhead (extra disk space and processing time). Indexes should be chosen carefully based on the following factors:

• Fields used in the WHERE clause: Indexing fields that are frequently used for filtering can significantly improve query performance.
• Cardinality: Fields with high cardinality (more unique values) are more suitable for indexing because they reduce the number of records returned, making binary searches more efficient.
• Unused fields in output: Avoiding indexing fields used only for output can save disk space and reduce processing time during insert and delete operations.

The above is the detailed content of How Does Database Indexing Improve Query Performance and When Should It Be Used?. For more information, please follow other related articles on the PHP Chinese website!