How to create index in database

Index is used to quickly find rows with a specific value in a column. Without using an index, MySQL must read the entire table starting from the first record until the relevant rows, the larger the table, the more time it takes to query the data. The steps to create an index are as follows:

Recommended tutorial: MySQL introductory video tutorial

1. First, we open a The data table to be operated is as shown in the figure below. We need to add an index to the name field.

#2. Next, we can add the index through create index, as shown in the figure below. Don’t throw away the content after on.

#3. Then we expand the index folder under the table and you can see the created index.

#4. Next, when we create the index, we can also add a sorting method after the field.

#5. Then use the drop statement when deleting the index, as shown in the figure below, which is similar to deleting the table.

#6. After the last drop statement is executed, there will be no index under the index folder.

Extended information

Indexes are used to speed up the retrieval of data rows in the table. Create a decentralized storage structure. The index is built for the table. It is composed of index pages other than the data page. The rows in each index page will contain logical pointers to speed up the retrieval of physical data. [2]

　In the database diagram, each index type can be created, edited, or deleted in the "Index/Key" property page of the selected table. Indexes are saved in the database when you save the table to which the index is attached, or when you save the graph in which the table resides.

In a relational database, an index is a separate, physical storage structure that sorts the values ​​of one or more columns in a database table. It is the index of one or several column values ​​in a table. Collection and corresponding list of logical pointers to the data pages in the table that physically identify these values. The index is equivalent to the table of contents of a book. You can quickly find the required content based on the page numbers in the table of contents.

Indexes provide pointers to data values ​​stored in specified columns of a table, and then sort these pointers according to the sort order you specify. The database uses an index to find a specific value and then points forward to find the row containing that value. This allows SQL statements corresponding to the table to execute faster and allows quick access to specific information in the database table.

When there are a large number of records in the table, if you want to query the table, the first way to search for information is to search the whole table, which is to take out all the records one by one, compare them one by one with the query conditions, and then return Records that meet the conditions will consume a lot of database system time and cause a lot of disk I/O operations; the second is to create an index in the table, then find the index value that meets the query conditions in the index, and finally save it in the index The ROWID (equivalent to the page number) in the table can quickly find the corresponding record in the table.

Advantages

1. Greatly speed up data retrieval;

2. Create a unique index to ensure the uniqueness of each row of data in the database table 3. Accelerate the connection between tables and tables; 4. When using grouping and sorting clauses for data retrieval, the time for grouping and sorting in queries can be significantly reduced.

Disadvantages

1. Indexes require physical space.

　2. When adding, deleting and modifying data in the table, the index must be maintained dynamically, which reduces the data maintenance speed.

The above is the detailed content of How to create index in database. For more information, please follow other related articles on the PHP Chinese website!