Thanks for the response. To be more specific: it's a very large
product table. It gets read a lot. It doesn't get written to very
often. So SELECT speed is essential. The table is fairly wide
because it contains a number of columns pointing to optional product
specification records in different tables. They are in different
tables because they have largely varying structure. Each is optional,
therefore these spec ID columns are nullable. There are also a few
columns referring to things that are constantly joined on, such as
brand ID's. The majority of queries use brand and other ID's for
inner joins and a number of other queries use left outer joins on the
spec ID columns.

To reiterate my question, I am not sure if it's better to have one
index that includes all of these spec, brand and other such columns,
or if I should have multiple indexes, each of which contains one
column, or something in between?

johnbandettini@yahoo.co.uk (John Bandettini) wrote in message news:<ed84d349.0309170058.500e8e73@posting.google. com>...
> There are a lot of points to be considered when selecting indexes.
>
> The size of the table, if you only have at most a few hundred rows on
> a table, you probably don't need any indexes as full scans will be
> pretty fast. On the other hand have millions of rows and good indexes
> will be essential unless you want your application to crawl.
>
> Know you application. You would think if foreign keys are defined they
> would need to be indexes, but not necessarily. Say one of your foreign
> keys is only used for a report that runs once a month at a weekend.
> Sure it will run faster with an applicable index, but do you need it
> to? What about the overhead everytime the table is updated, and
> needing to rebuild the index on a regular basis.
>
> My approach is to build the obvious ones. If you have a customer table
> for instance it's obvious that you need some kind of customer id
> index. I would monitor table usage in development and even continue
> into production using profiler to establish which would be the most
> used indexes. If you just create all the indexes you think you might
> need you could end up with 20, 30 or more indexes.
>
> I try to be fairly ruthless with indexes, trying to keep no more than
> 5 or 6 max on a table. It's not always possible, but the more you add
> the larger the space used, the more maint and the more overhead.
>
> Hope this helps
>
> John