Explanation: Multiple indexes on the same columns often indicate an inefficiency or error in database design. Sometimes multiple indexes are redundant, and redundant indexes may waste space in the database; they may also waste time every time a row is inserted or deleted, and every time the indexed columns are updated. Other times, multiple indexes aren't redundant, but they still might indicate a flaw in the database design.

Note: Versions 10 and later of SQL Anywhere do not create duplicate physical indexes when duplicate logical indexes are defined. However, Foxhound still warns about these duplicates because it may be a design issue. The duplication may affect performance; e.g., if one of the indexes is incorrect it may not be used to improve performance in the way it was intended. It may also affect integrity; e.g., if one of the indexes is a foreign key referring to the wrong column it may not be used to check referential integrity in the way it was intended.

Note: Versions 7 and later of SQL Anywhere automatically create a separate unique index for every PRIMARY KEY and UNIQUE column or table constraint that you define, as well as a separate non-unique index for every FOREIGN KEY constraint. There is no reason to explicitly create indexes on the same columns, and such indexes are redundant.

However, versions 5 and 6 of SQL Anywhere create a combined index for each PRIMARY KEY or UNIQUE constraint; this index "also contains the entries for all foreign keys that reference this table, whether those foreign keys appear in the same table or in a different one" (from the ASA 6 User's Guide, Chapter 25, The effect of column order in a composite index). These combined indexes can be much larger than an ordinary index on the primary key or unique constraint columns, and in those cases an explicitly created unique index on the same columns may improve performance. Such indexes are no longer needed in versions 7 and later.

The following discussion is written in terms of SQL Anywhere Version 9; i.e., separate indexes are automatically created for all primary keys, unique constraints and foreign keys.

Each list of two or more indexes is sorted by "relative importance" to help you choose which ones to keep: primary key first, then foreign key, UNIQUE constraint, UNIQUE index and finally non-unique index.

Each pair of multiple indexes should be considered separately, as follows:

• Primary key and unique constraint on the same columns: The unique constraint is redundant and should be dropped; a primary key acts as a unique constraint and is supported by a unique index. If the unique constraint is clustered, make the primary key clustered.
  
  
• Primary key and unique index on the same columns: The unique index is redundant and should be dropped. If the unique index is clustered, make the primary key clustered.
  
  
• Primary key and non-unique index on the same columns: The non-unique index is redundant and should be dropped. If the non-unique index is clustered, make the primary key clustered.
  
  
• Primary key and foreign key on the same columns: Determine why this table has the same columns in the primary key as in the parent table's primary key or unique constraint that is the target of the foreign key; there may be a design error. Otherwise, if the primary key and foreign key are both required, there's nothing that can be done about the fact the foreign key index is redundant from a performance point of view.
  
  
• Two unique constraints on the same columns: One unique constraint is redundant and should be dropped. If one of the unique constraints is clustered, drop the other one.
  
  
• Unique constraint and unique index on the same columns: The unique index is redundant and should be dropped; each unique constraint is supported by a unique index. If the unique index is clustered, make the unique constraint clustered.
  
  
• Unique constraint and non-unique index on the same columns: The non-unique index is redundant and should be dropped. If the non-unique index is clustered, make the unique constraint clustered.
  
  
• Unique constraint and foreign key on the same columns: Determine why this table has the same columns in the candidate key (unique constraint) as in the parent table's primary key or unique constraint that is the target of the foreign key; there may be a design error. Otherwise, if the unique constraint and foreign key are both required, there's nothing that can be done about the fact the foreign key index is redundant from a performance point of view.
  
  
• Two unique indexes on the same columns: One unique index is redundant and should be dropped. If one of the unique indexes is clustered, drop the other one.
  
  
• Unique index and non-unique index on the same columns: The non-unique index is redundant and should be dropped. If the non-unique index is clustered, make the unique index clustered.
  
  
• Unique index and foreign key on the same columns: Determine why this table has the same columns in the unique index as in the parent table's primary key or unique constraint that is the target of the foreign key; there may be a design error. Otherwise, if the unique index and foreign key are both required, there's nothing that can be done about the fact the foreign key index is redundant from a performance point of view.
  
  
• Two non-unique indexes on the same columns: One non-unique index is redundant and should be dropped. If one of the non-unique indexes is clustered, drop the other one.
  
  
• Non-unique index and foreign key on the same columns: The non-unique index is redundant and should be dropped. If the non-unique index is clustered, make the foreign key clustered.
  
  
• Two foreign keys on the same columns: If the same primary key or unique constraint in the same parent table is the target for both foreign keys, one of the foreign keys is redundant and should be dropped. Otherwise, determine why two different foreign key constraints on the same column are required; there may be a design error. If both foreign keys are required, there's nothing that can be done about the fact one of them is redundant from a performance point of view.