Indexes are easy to create but they are not always easy to manage but they speed up the search results. There are three types of indexes. The first is the PRIMARY KEY which is indexed by default and must be UNIQUE. The PRIMARY KEY can be either a single column or multiple columns as long as each row is UNIQUE. The standard is to have no more than three columns in the PRIMARY KEY. The next is the FOREIGN KEY which is a PRIMARY KEY from another table. If this connection is made through the database then the INDEX is made already. However, if this is not done then the last option can be used to create a manual INDEX. The following statements will create a normal INDEX and a UNIQUE INDEX.
|CREATE INDEX index_name ON table (field1)|
|CREATE UNIQUE INDEX index_name ON table (field1)|
The name of the INDEX must be unique. Different databases handle indexes differently. Some only work well with a few while others can have many on each table. It is also important to understand if the database has problems with a large number of PRIMARY to FOREIGN KEY pairs as this can inhibit performance.