Knowledge about database Indexes

An index is a structure that sorts the values of one or more columns in a database table, for example, the name 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. An index is a separate, physical database structure. It is a set of one or more column values in a table and a logical pointer list pointing to the data page that physically identifies these values in the table. 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. 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. Advantages of index creation: 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. 4. when you use grouping and sorting clauses to retrieve data, you can significantly reduce the time for grouping and sorting in queries. Index type: based on the database function, four types of indexes can be created in the Database Designer: unique index, non-unique index, primary key index, and clustered index. 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. Non-unique index: A non-unique index is a relatively unique index, which allows any two rows to have the same index value. When duplicate key values exist in existing data, the database allows you to save the newly created index with the table. In this case, the database cannot prevent adding new data that will create duplicate key values in the table. Primary Key Index: A database table often has a combination of columns or 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 (also called clustered index): In a clustered index, the physical order of the rows in the table is the same as that of the key-value Logic (index. 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.