Principles for indexing databases and data tables

Principles of database indexing
1, to determine whether the operation for the table is a large number of query operations or a large number of additions and deletions to change operations.

2, try to build an index to help a particular query. Check your own SQL statements to index the fields that frequently appear in the WHERE clause.

3, try to build a composite index to further improve system performance. Modifying a composite index consumes more time, and the composite index also occupies disk space.

4, for small tables, indexing may affect performance

5, you should avoid indexing fields that have fewer values.

6, avoid selecting columns of large data types as indexes.

Principles of Index Establishment

Index query is an important record query method in the database, either to enter the index and to build indexes on those fields to be considered in conjunction with the query requirements of the actual database system, here are some common principles in practice:

1. Create an index on a field that is often used as a filter;

2. Index The fields on the group by and ORDER by frequently in the SQL statement;

3. There is no need to index on fields with fewer values, such as the gender field;

4. Avoid indexing for frequently accessed columns;

5. Index The column used for the join (primary/external);

6. Create composite indexes on multiple columns that are frequently accessed, but be aware that the order in which the composite indexes are established is determined by the frequency of use;

7. The non-clustered index is established by default, but it is best to consider clustered indexes in the following cases, such as: a limited number (not very few) unique columns, a wide range of queries, the full use of the index can reduce the number of table scan i/0, effectively avoid the whole table search. Of course, a reasonable index should be based on the analysis and prediction of various queries, and also on the database structure designed by the DBA.

Principles for indexing databases and data tables