About indexes (content from CSDN member Dawugui)

There is no need to index all columns.
Index is a double-edged sword, there are good and bad. Give the information, you refer to:
1. Rational use of indexes
Index is an important data structure in database, and its basic aim is to improve the efficiency of query. Most of the database products now use IBM's first proposed ISAM index structure. The use of indexes is just right, with the following principles:
Indexes are established on columns that are frequently connected but not specified as foreign keys, while fields that are not frequently connected are automatically generated by the optimizer.
Index on a column that is frequently sorted or grouped (that is, a group by or order by operation).
A search is established on columns with more values that are often used in conditional expressions, and no index is established on columns with fewer values. For example, there are only two different values for "male" and "female" on the "Sex" column of the employee table, so there is no need to index. If indexing does not improve query efficiency, it can significantly reduce the speed of updates.
If there are multiple columns to be sorted, you can set up a composite index on those columns (compound index).
Use System Tools. If the Informix database has a Tbcheck tool, it can be checked on suspicious indexes. On some database servers, the index may fail or the read efficiency is reduced because of frequent manipulation, and if a query using an index slows down, try using the Tbcheck tool to check the integrity of the index and fix it if necessary. In addition, when a database table updates a large amount of data, deleting and rebuilding the index can increase the query speed.


(1) In the following two SELECT statements:
SELECT * FROM table1 where field1<=10000 and field1>=0;
SELECT * FROM table1 where field1>=0 and field1<=10000;
If the data in the datasheet is Field1 >=0, the first SELECT statement is much more efficient than the second SELECT statement because the first condition of the second SELECT statement consumes a significant amount of system resources.
The first principle: in the WHERE clause, the most restrictive condition should be placed at the front.


(2) In the following SELECT statement:
SELECT * from tab where a= ... and b= ... and c= ...;
In the case of indexed index (A,B,C), the order of the fields in the WHERE clause is consistent with the order of the fields in the index.
The second principle: the order of fields in the WHERE clause is consistent with the order of fields in the index.


The following assumes that there is a unique index I1 on the field1 and a field2 index I2.
(3) Select Field3,field4 from TB where field1= ' SDF ' fast
SELECT * from TB where field1= ' SDF ' is slow,
Because the latter is a step rowid table access after the index scan.


(4) Select Field3,field4 from TB where field1>= ' SDF ' fast
Select Field3,field4 from TB where field1> ' SDF ' slow
Because the former can quickly locate the index.


(5) Select Field3,field4 from TB where field2 like ' r% ' quick
Select Field3,field4 from TB where field2 like '%R ' slow,
Because the latter does not use an index.


(6) Use functions such as:
Select Field3,field4 from TB where upper (field2) = ' RMN ' does not use an index.
If a table has 20,000 records, it is recommended that you do not use a function, and if a table has more than 50,000 records, it is strictly forbidden to use the function. 20,000 records are not restricted below.


(7) Null values are not stored in the index, so
Select Field3,field4 from TB where field2 is[not] NULL does not use an index.


(8) Inequalities such as
Select Field3,field4 from TB where field2!= ' TOM ' does not use indexes.
Similarly,
Select Field3,field4 from terabytes where field2 not in (' M ', ' P ') does not use indexes.


(9) A multiple-column index that can be used only if the first column of the index in the query is used for the condition.


(a) Max,min functions such as
Select Max (field2) from TB uses indexes. So, if you need to take max,min,sum of the field, you should add an index.
Use only one aggregate function at a time, such as:
Select "Min" =min (field1), "Max" =max (field1) from TB
Why not: Select "min" = (select min (field1) from TB), "max" = (select Max (field1) from TB)


(11) Indexes with too many duplicate values are not used by the query optimizer. And because the index is built, and the index is modified when the field value is modified, the action to update the field is slower than without the index.


(12) An index value that is too large (such as an index on a field of char (40)) can cause a large amount of I/O overhead (even more than the I/O overhead of the table scan). Therefore, try to use an integer index. Sp_estspace can calculate the cost of tables and indexes.


(13) for a multiple-column index, the order by must be in accordance with the field order of the index.


(14) In Sybase, if the field of order by consists of a clustered index, there is no need to do order by. The records are arranged in the same order as the cluster index.