Question: The table ... has more than one index on columns ( ... )
Answer: 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.
|