Shulou(Shulou.com)06/01 Report--

Indexing a column is conditional on current events:

1. Columns that are frequently queried

2. Columns used in the order by clause

3. Columns that are foreign keys or primary keys

4. The column is the only column

5. Two or more columns often appear in the where clause or join conditions at the same time

In general, indexes should be created on these columns:

1 on the columns that often need to be searched, you can speed up the search

2 on the column as the primary key, force the uniqueness of the column and organize the arrangement structure of the data in the table

3 on the columns that are often used in joins, these columns are mainly foreign keys, which can speed up the connection

4 create an index on a column that often needs to search by range, because the index is sorted and its specified range is continuous

5 create an index on columns that often need to be sorted, because the index is already sorted, so that the query can take advantage of the sorting of the index to speed up the sorting query time.

6 create an index on the columns that are often used in the WHERE clause to speed up the judgment of conditions.

Similarly, indexes should not be created for some columns. In general, these columns that should not be indexed have the following characteristics:

First, indexes should not be created for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, indexing or no indexing does not improve query speed. On the contrary, due to the increase of the index, the maintenance speed of the system is reduced and the space requirement is increased.

Second, the index should not be added to columns that have only a few data values. This is because, because these columns have very few values, such as the gender column of the personnel table, the data rows of the result set account for a large proportion of the data rows in the table in the results of the query, that is, a large proportion of the data rows that need to be searched in the table. Increasing the index does not significantly speed up the retrieval speed.

Third, columns defined as text, p_w_picpath, and bit data types should not be indexed. This is because these columns either have a large amount of data or have very few values.

Fourth, indexes should not be created when modification performance is much greater than retrieval performance. This is because modification performance and retrieval performance contradict each other. When the index is added, the retrieval performance is improved, but the modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, indexes should not be created when modification performance is much greater than retrieval performance.

Depending on the functionality of the database, three indexes can be created in the database designer: unique index, primary key index, and clustered index.

Unique index

A unique index is an index that does not allow any two rows to have the same index value.

When there are duplicate key values in existing data, most databases do not allow the newly created unique index to be saved with the table. The database may also prevent the addition of new data that will create duplicate key values in the table. For example, if a unique index is created on the employee's last name (lname) in the employee table, no two employees can have the same last name. Primary key index database tables often have a column or combination of columns whose values uniquely identify each row in the table. This column is called the primary key of the table. Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a specific type of unique index. The index requires that each value in the primary key be unique. It also allows quick access to data when a primary key index is used in a query. Clustered index in a clustered index, the physical order of the rows in the table is the same as the logical (index) order of the key values. A table can contain only one clustered index.

If an index is not a clustered index, the physical order of rows in the table does not match the logical order of key values. Clustered indexes usually provide faster data access than nonclustered indexes.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.