Posted by othellomy on 03/27/07 04:29

On Mar 23, 11:08 pm, "Zamdrist" <zamdr...@gmail.com> wrote:
> I am tasked with maintaining a large database still on SQL Server 7.0.
> Performance is an issue not surprisingly and I've targeted two rather
> large tables to see what I can do with the indexes.
>
> The 2 tables are described as follows:
>
> MatterConflicts:
> Fields: MatterConflicts varchar(16), Matters varchar(16), HitMatters
> varchar(16), IsInclude varchar(1)
> Index: MatterConflicts
>
> MatterConflictHits:
> Fields: MatterConflictHits varchar(16), MatterConflicts varchar(16),
> ColumnLabel varchar(40), Hit varchar(100)
> Index: MatterConflictHits
>
> Now MatterConflicts row count is approaching 500K and
> MatterConflictHits is approaching 1 Million rows. There are only one
> index on each table, each for the table's primary key. The Matters
> field in MatterConflicts table joins back with a table that users
> access directly.


Drop the index on primary key and create a index on Matters field
instead since this is the column users are using to access data. Also,
Since these two tables are related, it will be worthwhile to consider
a index between the joining columns MatterConflicts of both tables.


> Question is, would it be beneficial to add, or modify the existing
> indexes for these tables to include both the primary and foreign keys,
> as well as an additional field?
>
> Doesn't seem to be to be very beneficial to have an index that only
> includes the primary key. So for example I'm thinking of creating an
> index for MatterConflicts that includes the fields: MatterConflicts,
> Matters, and HitMatters.

no. Don't use composite indexs as they seldom are useful.

> Thoughts? Suggestions? Thanks...

By the way, these tables are fairly large and you are right that you
have started to address the performance issue.

[Back to original message]