473,242 Members | 2,323 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,242 software developers and data experts.

indexing question

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.
Jul 20 '05 #1
4 2541
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.


Jul 20 '05 #2
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
Jul 20 '05 #3
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

Jul 20 '05 #4
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
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

21
by: Hilde Roth | last post by:
This may have been asked before but I can't find it. If I have a rectangular list of lists, say, l = ,,], is there a handy syntax for retrieving the ith item of every sublist? I know about for i...
6
by: Michael Drumheller | last post by:
(If you're not interested in NumArray, please skip this message.) I am new to NumArray and I wonder if someone can help me with array-indexing. Here's the basic situation: Given a rank-2 array...
1
by: Chuck Chekuri | last post by:
Hi, We have a 3TB oracle db. 24x7 operation with data constantly coming in to the DB. Users from all over the world use a web based applicaton to query and work on the data. Out transaction...
1
by: Danny | last post by:
sorry for posting a question that has probably been posted hundreds of times, but i can't seem to find the answer i need...We're using a Sql Server 7.0 database and recently started getting timeout...
1
by: Thomas Bartkus | last post by:
If we have a date/time field and are doing frequent queries WHERE {date/time field} BETWEEN TimeA AND TimeB Does it make sense, query speed wise, to create an index on the date/time field? ...
108
by: Bryan Olson | last post by:
The Python slice type has one method 'indices', and reportedly: This method takes a single integer argument /length/ and computes information about the extended slice that the slice object would...
7
by: Ryan | last post by:
I have a bit of a problem with regards an indexing strategy. Well, basically there is no indexing strategy on a set of data I have at work. Now, I didn't create the design as I would have allowed...
3
by: Chung Leong | last post by:
Here's the rest of the tutorial I started earlier: Aside from text within a document, Indexing Service let you search on meta information stored in the files. For example, MusicArtist and...
4
by: Emin | last post by:
Dear Experts, How much slower is dict indexing vs. list indexing (or indexing into a numpy array)? I realize that looking up a value in a dict should be constant time, but does anyone have a...
2
by: =?Utf-8?B?SmVycnkgQw==?= | last post by:
I have a server 2008 IIS 7.0 with indexing service installed. I have created the catalog and have a test page using these posts:...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.