Can indexing speed up querying data in the table?

First of all, understand why indexing will increase the speed. When DB executes a Sql statement, the default method is to scan the entire table based on the search conditions, and add the search result set if it encounters matching conditions.

#If we add an index to a certain field, the query will first locate the number of rows with a specific value in the index list, greatly reducing the number of traversal matches. The number of rows can significantly increase the query speed. (Recommended learning: MySQL video tutorial)

If you add an index, first query it in the index list, and our index list is the data structure of a Class B tree. The time complexity of the query is O(log2N). It will be very fast to locate the specific value row, so the query speed will be very fast.

Why is B-tree more suitable than B-tree for file indexing and database indexing of operating systems in practical applications?

1) B-tree's disk read and write costs are lower

The internal nodes of B-tree do not have pointers to specific information about keywords. Therefore, its internal nodes are smaller than B-tree. If all the keywords of the same internal node are stored in the same disk block, the more keywords the disk block can hold. The more keywords that need to be searched are read into the memory at one time. Relatively speaking, the number of IO reads and writes is reduced.

For example, assume that a disk block in the disk holds 16 bytes, a keyword is 2 bytes, and a keyword specific information pointer is 2 bytes. The internal nodes of a 9-order B-tree (one node can have up to 8 keywords) require 2 disk drives. The internal nodes of the B-tree only require 1 disk speed. When the internal nodes need to be read into the memory, the B-tree requires one more disk block search time (in the case of a disk, it is the disk rotation time) than the B-tree.

2) The query efficiency of B-tree is more stable

Because the non-terminal point is not the node that ultimately points to the file content, but only the keywords in the leaf nodes index of. Therefore, any keyword search must take a path from the root node to the leaf node. The path length of all keyword queries is the same, resulting in equal query efficiency for each data.

Index

Use indexes to quickly access specific information in a database table. An index is a structure that sorts the values ​​of one or more columns in a database table, such as the last name (lname) column of the employee table. If you want to find a specific employee by last name, an index will help you get that information faster than if you had to search all the rows in the table.

Note: Not all databases use indexes in the same way.

As a general rule, you only need to create an index on a table when the data in the indexed column is queried frequently. Indexes take up disk space and slow down adding, deleting, and updating rows. In most cases, the speed advantages of an index for data retrieval greatly outweigh its advantages.

For more MySQL related technical articles, please visit the MySQL Tutorial column to learn!

The above is the detailed content of Can indexing speed up querying data in the table?. For more information, please follow other related articles on the PHP Chinese website!