User's Guide

Part III. Using SQL Anywhere

Chapter 17. Working with Database Objects

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 commands) 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, however, 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 SQL 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.

When indexes on primary keys may be useful One case where an index on a key column may assist performance is when a large number of foreign keys reference a primary key. The performance increase is because of SQL Anywhere storage of key indexes.

SQL Anywhere automatically uses indexes to improve the performance of any database command whenever it can. There is no need to refer to indexes once they are created. SQL Anywhere also automatically updates the index 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 command.

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 the chapter "Monitoring and Improving Performance".
• For a detailed description of the CREATE INDEX command, including syntax and permission requirements, see "CREATE INDEX statement".
• For a detailed description of the DROP command, 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 popup 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 ISQL to browse the information in these tables.