Indexing principles in Database

--- Optimize database query efficiency using Indexes
1. The index cannot be created.
(1) frequently inserted, modified, and deleted tables

(2) A table with a small amount of data, because the time spent by the query optimizer in searching indexes may be greater than the time required to traverse the full table data.

2. Suitable for index creation
(1) Create an index for the columns in the WHERE clause
(2) create a composite index
(3) create an index for the columns in the group by clause

3. Design Principles of clustered Indexes
(1) The value of this column is unique or rarely repeated.
(2) between... and... columns are frequently used for sequential query.
(3) Unique column defining identity.
(4) columns that are often used to sort data.

--- Select statements that cannot use Indexes
1. Use a function for the index column, for example:
Select * from TB where Max (ID) = 100

2. Use '% xx' for the index column, for example:
Select * from TB where Id like '% 1'
Note that not all select statements using the like keyword cannot use indexes, such
Select * from TB where Id like '1% 'to use the index

3. Convert the column type in the WHERE clause (in fact, the function is also used)

4. The 1st columns of the composite index are not the most used columns. For example, create a composite index in the following three query statements, which includes col2, col1, and Id columns in sequence;
Select * from TB where id = '1' and col1 = 'A'
Select ID, sum (col1) from TB group by ID
Select * from TB where id = '2' and col2 = 'bb'
Therefore, indexes cannot be used in the first and second sentences. Therefore, pay attention to the order of combined indexes.

5. Some sentences using the in keyword in the WHERE clause
When nested select statements are used after the in keyword, indexes defined on the column cannot be used.
For example:
Select
*
From
Ta
Where
ID
In
(Select ID from TB where ....)

-- Indexes can be used in this way.
Select * from TB where ID in ('1', '2 ')

 

 

Turn: http://hi.csdn.net/fredrickhu