Using Indexes

Creating an Index

Indexes allow you to specify an index for a table to speed up searching. Primary keys are automatically indexed, but you can add indexes to other fields to allow them to be easily searchable. The database has to maintain the indexes, so it's not a good idea to make every field an index. The following example provides an index called keyIndex for a field called keywords.

INDEX keyIndex(Kewords)

You can specify how many characters you wish to index in a field. The following example creates an index on the first 100 characters of the field, Keywords.

INDEX keyIndex(Keywords(100))

Creating a Compound Index

You can create an index on more than one table by specifying a comma separated list. The following example creates a compound index on the first 100 characters of the Keywords field, and the Description field.

INDEX compoundIndex(Keywords(100), Description)

The indexes are from left to right. Therefore, in the example, indexes are only created for Descriptions where Keywords exist.

Creating Unique Indexes

The UNIQUE specifier may be used to ensure that indexes are unique. The following example requires that the values assigned to the first 100 characters of the Keywords field are unique.

UNIQUE INDEX keyIndex(Keywords(100))

The following example creates a table called search, with an Index on the first 100 characters of the Keywords field.

createSearch.sql

CREATE TABLE search
(
    Category VARCHAR(100) NOT NULL,
    Page VARCHAR(20) NOT NULL,
    Directory VARCHAR(255) NOT NULL,
    LinkName VARCHAR(255) NOT NULL,
    Kewords MEDIUMTEXT NOT NULL,
    Desription VARCHAR(255) NOT NULL,
    PRIMARY KEY(Page, Directory),
    INDEX keyIndex(Keywords(100))
);

The file may then be used with MySQL as follows:

mysql> \. createSearch.sql