Foxhound is the better* Database Monitor for SQL Anywhere. *better: More thorough, more relevant, more effective. ...more Alerts, more All Clears, more details, more control in your hands.

Tip 77: Sybase SQL Anywhere
Performance Tips & Techniques

9 - Create Indexes

A carefully chosen index can speed up WHERE, ORDER BY and GROUP BY clauses by giving SQL Anywhere a better way to find rows than sequentially scanning the entire table. On the other hand, an index takes up space and can slow down INSERT, DELETE and UPDATE statements because data must be saved in two places, the table and the index.

Here are some Rules of Thumb for indexes:

• Almost every table can benefit from an index.
• Always define a primary key.
• The primary key is almost never the only column that could benefit from an index.
• Don't create indexes on primary and foreign keys.
• Don't create more than half a dozen indexes for a single table.
• If an index is truly unique then declare it so.

Some of these rules may seem obvious ("Always define a primary key") and some might be confusing ("Don't create an index on a primary key"). And some of these rules and the reasons for breaking them are explained elsewhere in this article.

Figure 9 shows an example of CREATE INDEX where the index name is carefully chosen to be similar to but slightly different from the table name itself. Although it is possible to give the table and index exactly the same name to make it absolutely clear which index goes with which table, it is also important to tell them apart when reading the execution plan.

Figure 9 - An Example Of CREATE INDEX

CREATE TABLE test1 ( primary1 INTEGER NOT NULL, search1 INTEGER NOT NULL, search2 INTEGER NOT NULL, data1 INTEGER NOT NULL, PRIMARY KEY ( primary1 ) ); CREATE UNIQUE INDEX uitest1 ON test1 ( search1 ASC, search2 DESC );

---

[Home]

[Table of Contents]

[Previous Section]

[Next Section]