MySQL Tutorial

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

You might also like...

Comments

About the author

Gez Lemon United Kingdom

I'm available for contract work. Please visit Juicify for details.

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Programs must be written for people to read, and only incidentally for machines to execute.”