Since indexes speed up searches, it is often helpful for a table to have one index for each attribute (or combination of attributes) that is used frequently in searches. All indexes other than the primary index are called “secondary indexes”.
A table may have as many indexes as you like, as long as each index has a unique combination of columns, order of columns, and order of values (ASCending, DESCending). For example, in the code shown below, the third index duplicates the first and will either generate an error message or will waste disk space with duplicate information.
CREATE INDEX i1 ON TABLE t1 (col1, col2); -- The following is ok because although the columns are the same as in -- index i1, the order of the columns is different. CREATE INDEX i2 ON TABLE t1 (col2, col1); -- The following is not ok because index i3 would be exactly the -- same as index i1. CREATE INDEX i3 ON TABLE t1 (col1, col2); -- ERROR. -- The following is ok because although the columns and -- column order are the same, the order of the index values -- (ASCending vs. DESCending) is different. CREATE INDEX i3b ON TABLE t1 (col1, col2) DESC;
Note that if one index is a “leading subset” of another (meaning that the columns, column order, and value order of all N columns in index2 are exactly the same as the first N column(s) of index1), then you only need to create the index that is the superset. For example, suppose that you have an index on the combination of DEPARTMENT + OFFICE + EMP_NAME. This index can be used not only for searches by department, office and emp_name together, but also for searches of just the department, or just the department and office together. So there is no need to create a separate index on the department name alone, or on the department and office alone. The same is true for ORDER BY operations; if the ORDER BY criterion matches a subset of an existing index, then the server can use that index.
Keep in mind that if you defined a primary key or unique constraint, that key or constraint is implemented as an index. Thus you never need to create an index that is a “leading subset” of the primary key or of an existing unique constraint; such an index would be redundant.
Note that when searching using a secondary index, if the server finds all the requested data in the index key, the server does not need to look up the complete row in the table. (This applies only to “read” operations, that is, SELECT statements. If the user updates values in the table, then the data rows in the table as well as the values in the index(es) must be updated.)