By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 774 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

Does a Related Table Index (Duplicates OK) Option Speed Up Searches?

100+
P: 176
Or is it not necessary to have that option on the related table field, as the primary table indexed field does that job?

And is it a necessity to have the related table field to be indexed if I need distinct values only on that field in case there's a referential integrity between the primary and the related table, and in case there isn't one?

Thanks a bunch.
Feb 7 '07 #1
Share this Question
Share on Google+
4 Replies


100+
P: 176
Or is it not necessary to have that option on the related table field, as the primary table indexed field does that job?

And is it a necessity to have the related table field to be indexed if I need distinct values only on that field in case there's a referential integrity between the primary and the related table, and in case there isn't one?

Thanks a bunch.
Maybe I've phrased my question in a not so clearl way.
I'll rephrase it:

There's a primarty tblCities with CityID primary key field, and a related tblCustomers with CityID related field.

Primary table: tblCities -> Related table: tblCustomers
Primary Key field: CityID -> Related matching field: CityID

Is tblCustomers.CityId.Indexed Yes (Duplicates OK) setting required to speed up a search of a city in a customers table?
Feb 8 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Maybe I've phrased my question in a not so clearl way.
I'll rephrase it:

There's a primarty tblCities with CityID primary key field, and a related tblCustomers with CityID related field.

Primary table: tblCities -> Related table: tblCustomers
Primary Key field: CityID -> Related matching field: CityID

Is tblCustomers.CityId.Indexed Yes (Duplicates OK) setting required to speed up a search of a city in a customers table?
The short answer is yes. Also as it is involved in a relationship it should be indexed.

Indexing speeds up searches on the fields they are defined on, however too many indexes can also slow down the database.
Feb 8 '07 #3

100+
P: 176
The short answer is yes. Also as it is involved in a relationship it should be indexed.

Indexing speeds up searches on the fields they are defined on, however too many indexes can also slow down the database.
Thanks for your reply.
Feb 10 '07 #4

NeoPa
Expert Mod 15k+
P: 31,494
Indices (Indexes) will normally speed up access to tables when the data is required (for any of a multitude of reasons - including sort order; linking; UNION conjoining; etc) in that order. This speed increase can be very significant, especially on large datasets. The downside of this is that time is taken to maintain the indices whenever a change is made to data in the fields involved (Adding a new record always involves updating all the indices). Although there is a balance to be struck, it is usually true to say that adding an index is beneficial if it's likely to be used.
Feb 10 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.