User's Guide
   PART 1. Working with Databases
     CHAPTER 3. Working with Database Objects       

Working with indexes

Performance is an important consideration when designing and creating your database. Indexes can dramatically improve the performance of database searches (operations using SELECT, UPDATE, and DELETE statements) on specified columns.

When to use indexes 

An index is similar to a telephone book which first sorts people by their last name, and then sorts all the people with the same last name by their first name. Telephone books are indexed on the last name and first name. This speeds up searches for phone numbers given a particular last name. Just as a standard telephone book is no use at all for finding the phone number at a particular address, so an index is useful only for searches on a specific column or columns.

Indexes get more useful as the size of the table increases. The average time to find a phone number at a given address increases with the size of the phone book, while it does not take much longer to find the phone number of, say, K. Kaminski, in a large phone book than in a small phone book.

Use indexes for frequently-searched columns 

Indexes share one other feature with a phone book: they can take up a great deal of space for large data sets. For this reason, you should build indexes only for columns that are searched frequently or when disk space is not an issue.

If a column is already a primary key or foreign key, searches will be fast on this column because Adaptive Server Anywhere has facilities to optimize searches on these key columns. Thus, creating an index on a key column is not necessary and generally not recommended. If a column is only part of a key, an index may help.

Adaptive Server Anywhere automatically uses indexes to improve the performance of any database statement whenever it can. There is no need to refer to indexes once they are created. Also, the index is updated automatically when rows are deleted, updated or inserted.

Indexes are created on a specified table. You cannot create an index on a view.

If an index is no longer required, you can remove it from the database using the DROP statement.

Example 

In order to speed up a search on employee surnames in the sample database, you could create an index called EmpNames with the following statement:

CREATE INDEX EmpNames
ON employee (emp_lname, emp_fname)

The following statement removes the index from the database:

DROP INDEX EmpNames

For more information 

• For more information about improving database performance, including the use of indexes, see Monitoring and Improving Performance.

• For a detailed description of the CREATE INDEX statement, including syntax and permission requirements, see CREATE INDEX statement.

• For a detailed description of the DROP statement, including syntax and permission requirements, see DROP statement.

Creating and dropping indexes in Sybase Central 

  To create an index on a table in Sybase Central:

1. Connect to the database.

2. Double-click the table you wish to modify.

3. Double-click the Indexes folder, and then double-click Add Index.

4. Fill in the dialog box and click OK to complete.

You can drop an index in Sybase Central by right-clicking it, and selecting Delete from the pop-up menu.

     For more information, see the Sybase Central online Help.

  Indexes in the system tables

All the information about indexes in a database is held in the system tables SYS.SYSINDEX and SYS.SYSIXCOL. The information is presented in a more readable format in the system view SYS.SYSINDEXES. You can use Sybase Central or Interactive SQL to browse the information in these tables.