Creating Indexes

Usually, you create all the indexes you need when you are creating tables. Any column declared as PRIMARY KEY , KEY , UNIQUE , or INDEX will be indexed.

Sometimes you will find that you are running many queries based on an unindexed column, and in this situation, you can add an index using the CREATE INDEX statement.

Interestingly enough, the CREATE INDEX statement is mapped to an ALTER TABLE statement before being executed. The ALTER TABLE statement can be used for this and many other purposes. We will look at its use in the last section of this chapter.

We can, for example, add an index to the employee table as follows :

 create index name on employee(name); 

This creates an index called name based on the name field in the employee table.

There are not a great many options on the create index statement. We can precede the word index with UNIQUE to enforce a uniqueness constraint. We can also put the keyword FULLTEXT before index if we want to create a full-text index on a MyISAM table. (More on this in Chapter 9.)

The one other option is to limit indexes on char and varchar types to index just the first few characters in each field. You can do this by specifying the number of characters you want to be indexed in parentheses after the name of the index column, for example,

 create index part_name on employee(name(5)); 

The reason for this is that indexes on text types are not as efficient as indexes on numeric types, and just indexing the first few characters improves performance.