indexing question

[inline_four]

If I have a table with multiple foreign keys to various other tables,
what's the best way to index them: one index that contains all of
those columns, or multiple indexes containing one foreign key column
each. Don't know if this makes a difference, but my foreign keys are
not explicitly defined as such in the schema and most of them are
nullable.

[Steve Kass]

I'm not sure what you mean by saying you have foreign
keys but they aren't explicity defined in the schema,
but to answer your specific question, there is no one
right answer for any question of the form "how do I index ... ?"
It all depends on what queries you run against the data.

-- Steve Kass
-- Drew University
-- Ref: 6A4EC3A5-944A-42CD-833F-A85D797C5305

in*********@yahoo.com wrote:

If I have a table with multiple foreign keys to various other tables,
what's the best way to index them: one index that contains all of
those columns, or multiple indexes containing one foreign key column
each. Don't know if this makes a difference, but my foreign keys are
not explicitly defined as such in the schema and most of them are
nullable.

[John Bandettini]

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

[inline_four]

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?

jo************@yahoo.co.uk (John Bandettini) wrote in message news:<ed**************************@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

[John Bandettini]

You need to be careful with a composite index (index of more than one
column), they need to be in the right order to be useful. For instance
if you had a composite index that was made up of three columns,
cust-id, order-no and date in that order. If you had a query that
supplied the cust-id, order-no and date, the query would be very
likely to use the index. A query where you only had the cust-id, might
use the index, depending on the spread of cust-id. If however your
query only knew order-no and date it would not use the index as it did
not have a value for the first column (cust-id).

So in answer, it's would probably not be a good idea to put all of the
columns into one index, it would probably hardly ever use it. It is
more likely that you could create composite indexes of 2 or 3 columns
that may work and cut down on the number of indexes you need, but
again you really need to know your application to be sure.

One thing you can do with large composite indexes, is have 'covering'
indexes. If you have all the data columns that a query requires in an
index, the query can get its data from the index only without going to
the table itself. This can be very fast. Say you have a table with 100
columns in it and you have a query that runs a lot of times a day and
is only interested in three columns in the table. If you create an
index of those three columns, the query can get it's data from the
index without reading the table.

Hope this helps

John