Adding Indexes to Existing Tables

You don't have to add indexes at the same time that you create a table. You can add indexes to existing tables using the CREATE INDEX command. The exact syntax is very similar to the INDEX clause of a CREATE TABLE command:

CREATE INDEX index_name ON table_name(list, of, columns, ...) 

For example, in the book table, you might want to speed up ordering and matching operations on the title column. Adding an index to the title column is a good way to do this. To add the index, run the following query:

CREATE INDEX title ON book (title(16)); 

The index is explicitly limited to the first 16 characters of the title. This gives you an accurate index but is much more efficient for storage than placing an index of up to 255 characters.