Database index _ MySQL-mysql tutorial

Database index

You can use indexes to quickly access specific information in database tables. An index is a structure that sorts the values of one or more columns in a database table, for example, the last name (lname) column of the employee table. If you want to search for a specific employee by name, the index will help you get the information faster than all rows in the table that must be searched.

The index provides pointers to the data values stored in the specified column of the table, and then sorts these pointers according to the sort order you specify. The database uses an index in a similar way as you use an index in a book: it searches for an index to find a specific value, and then returns the pointer to the row containing the value.

In the database graph, you can create, edit, or delete each index type on the index/key attribute page of the selected table. When you save the table to which the index is attached or the relational graph of the table is saved, the index is saved in the database. For more information, see create an index.

Note: not all databases use indexes in the same way. For more information, see database server considerations or database documentation.
As a general rule, an index must be created on a table only when data in the index column is frequently queried. Indexes occupy disk space and speed up adding, deleting, and updating rows. In most cases, the speed advantage of indexing for data retrieval is much higher than that of indexing.

Index column
You can create an index based on a single or multiple column in a database table. Multiple-column indexes enable you to differentiate rows with the same value in one of the columns.

If you often search for two or more columns at the same time or sort by two or more columns, the index is also helpful. For example, if you often set a criterion for the first and second columns in the same query, it makes sense to create multiple columns of indexes in these two columns.

Determine the validity of the index:

Check the WHERE and JOIN clauses of the query. Each column in any clause is an object that can be selected by the index.
Test the new index to check its impact on running query performance.
Consider the number of indexes created on the table. It is best to avoid having many indexes on a single table.
Check the definitions of indexes created on the table. It is best to avoid overlapping indexes that contain shared columns.
Check the number of unique data values in a column and compare the quantity with the number of rows in the table. The comparison result is the selectivity of the column, which helps to determine whether the column is suitable for creating an index. if so, determine the index type.
Index type
Based on the functions of the database, you can create three indexes in the database designer: unique index, primary key index, and clustered index. For more information about the index functions supported by the database, see the database documentation.

Tip: Although the unique index helps to locate information, we recommend that you use primary keys or unique constraints to obtain the best performance results.
Unique index
A unique index is an index that does not allow any two rows to have the same index value.

When duplicate key values exist in existing data, most databases do not allow you to save the newly created unique index with the table. The database may also prevent adding new data that will create duplicate key values in the table. For example, if the employee's last name (lname) in the employee table creates a unique index, neither employee can have the same name.

Primary key index

A database table often has a column or a combination of columns. its values uniquely identify each row in the table. This column is called the primary key of the table.

When you define a primary key for a table in the database relationship diagram, the primary key index is automatically created. the primary key index is a specific type of unique index. This index requires that each value in the primary key be unique. When a primary key index is used in a query, it also allows quick access to data.

Clustered index

In the clustered index, the physical order of the row in the table is the same as the logic (index) order of the key value. A table can contain only one clustered index.

If an index is not a clustered index, the physical sequence of the row in the table does not match the logical sequence of the key value. Compared with non-clustered indexes, clustered indexes generally provide faster data access speeds.