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.


[Home] [Table of Contents] [Previous Section] [Next Section]

Breck Carter
Last modified: February 12, 1998
mail to: bcarter@bcarter.com


Tip 77: Sybase SQL Anywhere
Performance Tips & Techniques


12 - Trust Your Keys

In most cases you shouldn't create indexes on primary and foreign key columns even if these columns are frequently used in joins, searches and ORDER BY clauses. That's because SQL Anywhere already creates a special index for each primary key. This index contains entries for each primary key value and every foreign key that points to it, and it automatically speeds up the following operations:

The same kind of special index is created for columns that appear in a UNIQUE constraint. That means you don't have to create a separate index for those columns either, or for foreign keys which point at that UNIQUE constraint instead of a primary key. (Yes, it's true, foreign keys don't have to point to primary key columns. And a UNIQUE constraint is not quite the same as a UNIQUE index even though a unique index is automatically created for a UNIQUE constraint.)

Figure 12A shows I/O estimates of only 2 and 3 for queries involving primary key and unique columns while the third estimate is a much higher 182 for a SELECT using an ordinary non-indexed column.

Figure 12A - Primary Keys And Unique Constraints Are Indexed 
CREATE TABLE parent1

   ( primary1 INTEGER NOT NULL,

     unique1 INTEGER NOT NULL,

     data1 INTEGER NOT NULL,

     PRIMARY KEY ( primary1 ),

     UNIQUE ( unique1 ) );


SELECT * FROM parent1 WHERE primary1 = 44;

   Estimated 1 rows in query (I/O estimate 2)

   PLAN> parent1 (parent1)


SELECT * FROM parent1 WHERE unique1 = 44;

   Estimated 1 rows in query (I/O estimate 3)

   PLAN> parent1 (parent1 UNIQUE (unique1))


SELECT * FROM parent1 where data1 = 44;

   Estimated 3 rows in query (I/O estimate 182)

   PLAN> parent1 (seq)

Figure 12B shows an I/O estimate of only 3 for a SELECT using a foreign key column while the estimate is 326 for a query using an ordinary non-indexed column. Note that there is nothing special about child1.primary1 except for its appearance in the foreign key specification. In particular, child1.primary1 does not appear in any primary key or unique constraint definition for the child table. It is the special primary-and-foreign-key index for the other table parent1 that makes the query on child1.primary1 run faster.

Figure 12B - Foreign Keys Are Indexed Too 
CREATE TABLE child1

   ( primary2 INTEGER NOT NULL,

     primary1 INTEGER NOT NULL,

     data1 INTEGER NOT NULL,

     PRIMARY KEY ( primary2 ),

     FOREIGN KEY ( primary1 )

        REFERENCES parent1 ( primary1 ) );


SELECT * FROM child1 WHERE primary1 = 44;

   Estimated 1 rows in query (I/O estimate 3)

   PLAN> child1 (parent1)


SELECT * FROM child1 WHERE data1 = 44;

   Estimated 3 rows in query (I/O estimate 326)

   PLAN> child1 (seq)

The bottom line is this: If you don't need a second index on primary or foreign key columns then don't create it. Having two identical indexes won't help retrievals but it will slow down updates. If you're using a design tool to create your tables make sure that it doesn't create redundant primary and foreign key indexes for you.

[Home] [Table of Contents] [Previous Section] [Next Section]