FK Index Advise needed
-
I have a couple custom database that I am building. They will be 3NF with several many to many linking tables.
In general, both databases will have about 30 tables. MOST tables will have an autonumber PK, and two FK fields related to PK's on another table. My question is about indexing...
I will not be designing the application(s) using the DB's (as far as ASP code, VB script) etc, so I am not familiar with the SQL to be used, nor am I confident that my developers will run the code by me before the system is done.
Therefore, I am trying to create very general indexes on each table that will serve generic needs (my attempt at half-way optimizing the DB). So each table has a unique index on the PK, and each FK has it OWN non-unique index. Is this the correct way to do this? Or, should I have one index that covers both FK columns rather than two indexes? Any thoughts? Please provide some reasoning with any answers.
Thanks in advance!
Ryan Hunt
-
"Therefore, I am trying to create very general indexes on each table that will serve generic needs (my attempt at half-way optimizing the DB). So each table has a unique index on the PK, and each FK has it OWN non-unique index. Is this the correct way to do this?"
Yes, that's the best way to start with.
Other indexes can be added later on.
Also define your unique constraints if you are using e.g. identifier-columns to generate your PK, other column(s) will determine a unique attribute for your entity. (this is documented in your 3NF-ERD).
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Another important point is to determine which fields will be searched frequently. Make sure that any fields that will be in the where clause often are indexed. This will cause the table to use the indexes in most cases.
Make sure the get the Clustered/non-Clustered right also. Clustered indexes are most efficient when searching for values in a range (like date ranges) non-clustered indexes are most effiicient when searching a specific value (like in a foreign key)
Hope this helps.
Kevin MCSD
-
Kevin, I don't know (and may not know) what columns will show up in the where clause of the SQL. My programmers often don't provide me with their code. If a table has two foreign keys that I am confident will often show up in where clauses (both by themselves, and together) am I better of creating a composite index, or one index for each.
I am asking for two reasons, If an SQL statement's where clause has both columns in it, while the optimizer use two separate indexes (and at what cost). Also, If I use a composite index and only one column is used in a where statement (let's say the column to the far right (the second column of the index)) will the optimizer use the composite key to perform the request?
These are some of the issues I am trying to work out.
Thanks
Ryan
-
The stats for a composite index are based on the first item in the index, So place you most unique first to the least unique and it should. You still will need to test to be 100% sure in you particular case, but Index chooses are based on statistics for the most part.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply