After doing some reading, I'd like some expert opinions on choosing indexes
for several scenarios.
1) One book said that you should almost index foreign keys in a table since
lookups are more inefficient without them. I have tables with up to ten FKs
used mostly for lookups from/joins with the PK table (e.g. marital status,
education level, etc.). Each of these PK lookup tables only have 3-10
possible values. However, pulling info from these PKs to display the linked
items is a frequent event and many times a day a massive report effectively
denormalizes everything for any individual customer.
Generally, is it worth the overhead to index foreign keys in one table for
primary key tables that have a handful of values?
2) If I have a web application for numerous companies that each have their
own customers, and when the folks from one company are using the application
they only see their customers one at a time or an alphabetical list (a
pretty common scenario I would think), what is the best way to index
CompanyID (a FK to a Company PK table), CustLastName, CustFirstName?
Is it better to have three distinct indexes? Which one should be the optimal
for a clustered index? (I would think CompanyID since it is used for almost
every query except for individual customers which are already indexed by
being a PK as CustomerID)
Or, should all of these be rolled into one composite index? If these are
rolled into a composite index, is there any value at all for still having
distinct indexes for these three columns? Would you still need an index for
the company FK join to the PK Company table, e.g. to display the company
name?
Even though the CompanyID is not as selective as the combination of first
name and last name, I would think a clustered index of CompanyID,
CustLastName, CustFirstName would do the trick without any other indices.
But maybe a separate index for CompanyID (for company information joins)?
Thanks for any tips.1) yes, otherwise you will end up doing table scans. With the indexes in
place you will scan the index which is much more efficient.
2) yes, always go from most restrictive to least restrict. You probably have
more first names, than last names and more last names than companies, hence
the index should look like companyID, LastName, Firstname. I would use one
index but it really depends. If most of your queries are like this
select * from tablename where companyid=1 and lastname =smith and
firstname=don the index should be on companyid, lastname and then first
name, however if you query on lastnames or firstnames you should have an
index on each of these columns as well.
Use a clustered index if you are doing a scan ie select * from tablename
where companyid>100, otherwise if you are only returning a few values use a
non clustered index.
HTH
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Don Miller" <nospam@.nospam.com> wrote in message
news:Obu59EP1GHA.324@.TK2MSFTNGP05.phx.gbl...
> After doing some reading, I'd like some expert opinions on choosing
> indexes
> for several scenarios.
> 1) One book said that you should almost index foreign keys in a table
> since
> lookups are more inefficient without them. I have tables with up to ten
> FKs
> used mostly for lookups from/joins with the PK table (e.g. marital status,
> education level, etc.). Each of these PK lookup tables only have 3-10
> possible values. However, pulling info from these PKs to display the
> linked
> items is a frequent event and many times a day a massive report
> effectively
> denormalizes everything for any individual customer.
> Generally, is it worth the overhead to index foreign keys in one table for
> primary key tables that have a handful of values?
> 2) If I have a web application for numerous companies that each have their
> own customers, and when the folks from one company are using the
> application
> they only see their customers one at a time or an alphabetical list (a
> pretty common scenario I would think), what is the best way to index
> CompanyID (a FK to a Company PK table), CustLastName, CustFirstName?
> Is it better to have three distinct indexes? Which one should be the
> optimal
> for a clustered index? (I would think CompanyID since it is used for
> almost
> every query except for individual customers which are already indexed by
> being a PK as CustomerID)
> Or, should all of these be rolled into one composite index? If these are
> rolled into a composite index, is there any value at all for still having
> distinct indexes for these three columns? Would you still need an index
> for
> the company FK join to the PK Company table, e.g. to display the company
> name?
> Even though the CompanyID is not as selective as the combination of first
> name and last name, I would think a clustered index of CompanyID,
> CustLastName, CustFirstName would do the trick without any other indices.
> But maybe a separate index for CompanyID (for company information
> joins)?
> Thanks for any tips.
>|||Thanks for the advice, but I'm not sure if I understand all of your answers.
> 1) yes, otherwise you will end up doing table scans. With the indexes in
> place you will scan the index which is much more efficient.
Even faster than a table scan of only 5-10 rows total?
> 2) yes, always go from most restrictive to least restrict. You probably
have
> more first names, than last names and more last names than companies,
hence
> the index should look like companyID, LastName, Firstname.
So, if you go from most restrictive to least restrictive shouldn't the index
be in the exact opposite order? (e.g. FirstName, LastName, CompanyID)
> I would use one
> index but it really depends. If most of your queries are like this
> select * from tablename where companyid=1 and lastname =smith and
> firstname=don the index should be on companyid, lastname and then first
> name,
Most of my queries are probably more like this
select * from tablename where companyID = 1 order by lastname, firstname
> Use a clustered index if you are doing a scan ie select * from tablename
> where companyid>100, otherwise if you are only returning a few values use
a
> non clustered index.
The companyID will always be just one value and not a range. With that said,
maybe the LastName, FirstName should be the clustered choice with a
non-clustered index for companyID?
Thanks again for your help.|||Hi Hilary
Why is a clustered index better for a scan? Aren't all indexes sorted &
therefore equally beneficial for scans? Non-clustered actually indexes more
efficient for scans due to their higher page / row storage density..
The only thing a clustered index can do better than a non-clustered index is
cover the SELECT *, as it contains all columns, which definitely makes your
advice right in this case, but I think your rationale isn't clear.
I'm starting a series of short articles, intended to debunk the many myths
about clustered indexes on my blog, including the classic "clustered indexes
are better for range scans". I started this series a couple of days ago,
covering the bookmark lookup problem but I'll cover this specific issue soon
(it's definitely bogus):
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/10/363.aspx
Regards,
Greg Linwood
SQL Server MVP
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OUziSWQ1GHA.1256@.TK2MSFTNGP04.phx.gbl...
> 1) yes, otherwise you will end up doing table scans. With the indexes in
> place you will scan the index which is much more efficient.
> 2) yes, always go from most restrictive to least restrict. You probably
> have more first names, than last names and more last names than companies,
> hence the index should look like companyID, LastName, Firstname. I would
> use one index but it really depends. If most of your queries are like this
> select * from tablename where companyid=1 and lastname =smith and
> firstname=don the index should be on companyid, lastname and then first
> name, however if you query on lastnames or firstnames you should have an
> index on each of these columns as well.
> Use a clustered index if you are doing a scan ie select * from tablename
> where companyid>100, otherwise if you are only returning a few values use
> a non clustered index.
> HTH
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:Obu59EP1GHA.324@.TK2MSFTNGP05.phx.gbl...
>> After doing some reading, I'd like some expert opinions on choosing
>> indexes
>> for several scenarios.
>> 1) One book said that you should almost index foreign keys in a table
>> since
>> lookups are more inefficient without them. I have tables with up to ten
>> FKs
>> used mostly for lookups from/joins with the PK table (e.g. marital
>> status,
>> education level, etc.). Each of these PK lookup tables only have 3-10
>> possible values. However, pulling info from these PKs to display the
>> linked
>> items is a frequent event and many times a day a massive report
>> effectively
>> denormalizes everything for any individual customer.
>> Generally, is it worth the overhead to index foreign keys in one table
>> for
>> primary key tables that have a handful of values?
>> 2) If I have a web application for numerous companies that each have
>> their
>> own customers, and when the folks from one company are using the
>> application
>> they only see their customers one at a time or an alphabetical list (a
>> pretty common scenario I would think), what is the best way to index
>> CompanyID (a FK to a Company PK table), CustLastName, CustFirstName?
>> Is it better to have three distinct indexes? Which one should be the
>> optimal
>> for a clustered index? (I would think CompanyID since it is used for
>> almost
>> every query except for individual customers which are already indexed by
>> being a PK as CustomerID)
>> Or, should all of these be rolled into one composite index? If these are
>> rolled into a composite index, is there any value at all for still having
>> distinct indexes for these three columns? Would you still need an index
>> for
>> the company FK join to the PK Company table, e.g. to display the company
>> name?
>> Even though the CompanyID is not as selective as the combination of first
>> name and last name, I would think a clustered index of CompanyID,
>> CustLastName, CustFirstName would do the trick without any other indices.
>> But maybe a separate index for CompanyID (for company information
>> joins)?
>> Thanks for any tips.
>>
>|||On Sun, 10 Sep 2006 10:58:40 -0500, Don Miller wrote:
>After doing some reading, I'd like some expert opinions on choosing indexes
>for several scenarios.
Hi Don,
Here are mine. They are the diametrically opposite of Hilary's opinions,
just to confuse you <eg>
>1) One book said that you should almost index foreign keys in a table since
>lookups are more inefficient without them. I have tables with up to ten FKs
>used mostly for lookups from/joins with the PK table (e.g. marital status,
>education level, etc.). Each of these PK lookup tables only have 3-10
>possible values. However, pulling info from these PKs to display the linked
>items is a frequent event and many times a day a massive report effectively
>denormalizes everything for any individual customer.
>Generally, is it worth the overhead to index foreign keys in one table for
>primary key tables that have a handful of values?
In cases like this, with the referred table holding marital status,
education level, etc, indexes on the foreign key columns are often not
needed. Though it depends on the nature of your queries. If you often
query for female university level people or similar, indexes on those
columns *MIGHT* get used. But for most queries, I expect SQL Server to
filter rows based on other (more restrictive) criteria in the SELECT,
then use bookmark lookup or a hash join to find the english name for the
gender code, marital status code and education level code.
Indexes on foreign key columns are mainly used to check the constraint
on changes in the referenced table (which, in the case of marital status
and education level, should be extremely rare - the contents of those
tables are alomst constant) or when a very efficient filter can be
applied to the referenced table (which is often the case between
referencing table that hold non-constant data, such as orders and order
details, or orders and customers).
>2) If I have a web application for numerous companies that each have their
>own customers, and when the folks from one company are using the application
>they only see their customers one at a time or an alphabetical list (a
>pretty common scenario I would think), what is the best way to index
>CompanyID (a FK to a Company PK table), CustLastName, CustFirstName?
For queries like that, a clustered index no (CompanyID, CustLastName,
CustFirstName) is best. The engine can use the index structure to find
the first matching row in an instant; after that, it can navigate the
logical order of the data in the clustered index to find all rows for
the same company, and they'll already be in the order required. (It
might help to include CompanyID as the first column in the ORDER BY
clause.)
Note that, contrary to popular belief, more or less restrictive should
NOT be the first consideration for the order of columns in an index. You
should start with hhow the columns are used; restrictiveness comes only
second. If most of your searches are based on last name only, then last
name should be the first column in the index, whether restrictive or
not. If most searches are based on all three columns, then the order
doesn't matter for those searches (though statistics will be slightly
better if the most restrictive column is first). If all kinds of
searches are mixed, then you should have the most restrictive column as
the first in the index - and you might consider addition indexes for
searches that can't use the index.
>Is it better to have three distinct indexes? Which one should be the optimal
>for a clustered index? (I would think CompanyID since it is used for almost
>every query except for individual customers which are already indexed by
>being a PK as CustomerID)
I don't think three seperate indexes is a good idea. That might work if
you often search on first name only, often search on last name only, and
often search on CompanyID only. For searches on two or all three
columns, SQL Server *can* use operations that use all three tables, then
combine the intermediate results from those index searches - but the
overhead of that is quite costly, so it'll very often just use one index
to narrow down the search a bit, then search the rest by accessing all
rows and checking the values.
If you do use three indexes, than I think that CompanyID might indeed be
the best choice, but I'd try different options to make sure. Of course,
this also depends on the nature of other queries - above, you outlines
one typical query, but iff there are others as well, you should include
them in your analysis.
And I agree that searches for individual customers would benefit from an
index on CustomerID; since this is your key, the index shoould already
exist. If you've used default options, it's the clustered index - I
suggest that yoou change it to a nonclustered index, so that you can
have (CompanyID, CustLastName, CustFirstName) as clustered index. The
search for an individual customer results in just a single bookmark
lookup, which is quite inexpensive. It's the searches for a range of
rows where repeated bookmark lookups get expensive.
>Or, should all of these be rolled into one composite index? If these are
>rolled into a composite index, is there any value at all for still having
>distinct indexes for these three columns? Would you still need an index for
>the company FK join to the PK Company table, e.g. to display the company
>name?
Some situations might benefit from additional indexes on the single
columns (especially on the columns that are NOT first in the composite
index).
Finding company name based on CompanyID will in almost all cases require
an index on the CompanyID column in the Companies table - which should
be there already if you didn;t forget the primary key constraint.
>Even though the CompanyID is not as selective as the combination of first
>name and last name, I would think a clustered index of CompanyID,
>CustLastName, CustFirstName would do the trick without any other indices.
>But maybe a separate index for CompanyID (for company information joins)?
With that clustered index, a seperate index on only CompanyID would only
give you some benefit in rare cases where the query is covered by this
index - the benefit will probably not outweigh the cost of maintaining
an additional index.
--
Hugo Kornelis, SQL Server MVP|||1) well probably not. But for larger tables indexes are faster than table
scans.
2) Well when I think most restrictive I think of a value which returns the
fewest results. So as there are fewer company id's than Smiths and even
fewer company id's than Johns, I would argue that it would go some
companyID, LastName and then FirstName.
So consider John Smith where there are 200 john smiths per company id and
there are 20 companies. If you search on FirstName, LastName, CompanyID you
would return 200*20 rows to the filtering condition which filters out
company id's. Then 200 are returned. If you go Company ID, then Names, 200
rows are returned to the filtering condition and then 200 remain in the
filter.
Make sense?
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Don Miller" <nospam@.nospam.com> wrote in message
news:uXL6NgQ1GHA.4648@.TK2MSFTNGP04.phx.gbl...
> Thanks for the advice, but I'm not sure if I understand all of your
> answers.
>> 1) yes, otherwise you will end up doing table scans. With the indexes in
>> place you will scan the index which is much more efficient.
> Even faster than a table scan of only 5-10 rows total?
>> 2) yes, always go from most restrictive to least restrict. You probably
> have
>> more first names, than last names and more last names than companies,
> hence
>> the index should look like companyID, LastName, Firstname.
> So, if you go from most restrictive to least restrictive shouldn't the
> index
> be in the exact opposite order? (e.g. FirstName, LastName, CompanyID)
>> I would use one
>> index but it really depends. If most of your queries are like this
>> select * from tablename where companyid=1 and lastname =smith and
>> firstname=don the index should be on companyid, lastname and then first
>> name,
> Most of my queries are probably more like this
> select * from tablename where companyID = 1 order by lastname, firstname
>> Use a clustered index if you are doing a scan ie select * from tablename
>> where companyid>100, otherwise if you are only returning a few values use
> a
>> non clustered index.
> The companyID will always be just one value and not a range. With that
> said,
> maybe the LastName, FirstName should be the clustered choice with a
> non-clustered index for companyID?
> Thanks again for your help.
>|||Great answer Hugo, better than mine.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:fj39g258fjaagokp4k9ci2r2flbqkpindb@.4ax.com...
> On Sun, 10 Sep 2006 10:58:40 -0500, Don Miller wrote:
>>After doing some reading, I'd like some expert opinions on choosing
>>indexes
>>for several scenarios.
> Hi Don,
> Here are mine. They are the diametrically opposite of Hilary's opinions,
> just to confuse you <eg>
>>1) One book said that you should almost index foreign keys in a table
>>since
>>lookups are more inefficient without them. I have tables with up to ten
>>FKs
>>used mostly for lookups from/joins with the PK table (e.g. marital status,
>>education level, etc.). Each of these PK lookup tables only have 3-10
>>possible values. However, pulling info from these PKs to display the
>>linked
>>items is a frequent event and many times a day a massive report
>>effectively
>>denormalizes everything for any individual customer.
>>Generally, is it worth the overhead to index foreign keys in one table for
>>primary key tables that have a handful of values?
> In cases like this, with the referred table holding marital status,
> education level, etc, indexes on the foreign key columns are often not
> needed. Though it depends on the nature of your queries. If you often
> query for female university level people or similar, indexes on those
> columns *MIGHT* get used. But for most queries, I expect SQL Server to
> filter rows based on other (more restrictive) criteria in the SELECT,
> then use bookmark lookup or a hash join to find the english name for the
> gender code, marital status code and education level code.
> Indexes on foreign key columns are mainly used to check the constraint
> on changes in the referenced table (which, in the case of marital status
> and education level, should be extremely rare - the contents of those
> tables are alomst constant) or when a very efficient filter can be
> applied to the referenced table (which is often the case between
> referencing table that hold non-constant data, such as orders and order
> details, or orders and customers).
>>2) If I have a web application for numerous companies that each have their
>>own customers, and when the folks from one company are using the
>>application
>>they only see their customers one at a time or an alphabetical list (a
>>pretty common scenario I would think), what is the best way to index
>>CompanyID (a FK to a Company PK table), CustLastName, CustFirstName?
> For queries like that, a clustered index no (CompanyID, CustLastName,
> CustFirstName) is best. The engine can use the index structure to find
> the first matching row in an instant; after that, it can navigate the
> logical order of the data in the clustered index to find all rows for
> the same company, and they'll already be in the order required. (It
> might help to include CompanyID as the first column in the ORDER BY
> clause.)
> Note that, contrary to popular belief, more or less restrictive should
> NOT be the first consideration for the order of columns in an index. You
> should start with hhow the columns are used; restrictiveness comes only
> second. If most of your searches are based on last name only, then last
> name should be the first column in the index, whether restrictive or
> not. If most searches are based on all three columns, then the order
> doesn't matter for those searches (though statistics will be slightly
> better if the most restrictive column is first). If all kinds of
> searches are mixed, then you should have the most restrictive column as
> the first in the index - and you might consider addition indexes for
> searches that can't use the index.
>>Is it better to have three distinct indexes? Which one should be the
>>optimal
>>for a clustered index? (I would think CompanyID since it is used for
>>almost
>>every query except for individual customers which are already indexed by
>>being a PK as CustomerID)
> I don't think three seperate indexes is a good idea. That might work if
> you often search on first name only, often search on last name only, and
> often search on CompanyID only. For searches on two or all three
> columns, SQL Server *can* use operations that use all three tables, then
> combine the intermediate results from those index searches - but the
> overhead of that is quite costly, so it'll very often just use one index
> to narrow down the search a bit, then search the rest by accessing all
> rows and checking the values.
> If you do use three indexes, than I think that CompanyID might indeed be
> the best choice, but I'd try different options to make sure. Of course,
> this also depends on the nature of other queries - above, you outlines
> one typical query, but iff there are others as well, you should include
> them in your analysis.
> And I agree that searches for individual customers would benefit from an
> index on CustomerID; since this is your key, the index shoould already
> exist. If you've used default options, it's the clustered index - I
> suggest that yoou change it to a nonclustered index, so that you can
> have (CompanyID, CustLastName, CustFirstName) as clustered index. The
> search for an individual customer results in just a single bookmark
> lookup, which is quite inexpensive. It's the searches for a range of
> rows where repeated bookmark lookups get expensive.
>>Or, should all of these be rolled into one composite index? If these are
>>rolled into a composite index, is there any value at all for still having
>>distinct indexes for these three columns? Would you still need an index
>>for
>>the company FK join to the PK Company table, e.g. to display the company
>>name?
> Some situations might benefit from additional indexes on the single
> columns (especially on the columns that are NOT first in the composite
> index).
> Finding company name based on CompanyID will in almost all cases require
> an index on the CompanyID column in the Companies table - which should
> be there already if you didn;t forget the primary key constraint.
>>Even though the CompanyID is not as selective as the combination of first
>>name and last name, I would think a clustered index of CompanyID,
>>CustLastName, CustFirstName would do the trick without any other indices.
>>But maybe a separate index for CompanyID (for company information
>>joins)?
> With that clustered index, a seperate index on only CompanyID would only
> give you some benefit in rare cases where the query is covered by this
> index - the benefit will probably not outweigh the cost of maintaining
> an additional index.
> --
> Hugo Kornelis, SQL Server MVP|||Hi Greg!
I have been putting together a presentation on indexing fundamentals for
some of the code camps. In some tests I ran it seems to me that clustered
indexes were better for scans, however I will have to try to repeat this to
verify it. I look forward to your research.
I'll be posting something in the private group soon which I have discovered
which I think it pretty interesting.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OHZdTlS1GHA.1300@.TK2MSFTNGP05.phx.gbl...
> Hi Hilary
> Why is a clustered index better for a scan? Aren't all indexes sorted &
> therefore equally beneficial for scans? Non-clustered actually indexes
> more efficient for scans due to their higher page / row storage density..
> The only thing a clustered index can do better than a non-clustered index
> is cover the SELECT *, as it contains all columns, which definitely makes
> your advice right in this case, but I think your rationale isn't clear.
> I'm starting a series of short articles, intended to debunk the many myths
> about clustered indexes on my blog, including the classic "clustered
> indexes are better for range scans". I started this series a couple of
> days ago, covering the bookmark lookup problem but I'll cover this
> specific issue soon (it's definitely bogus):
> http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/10/363.aspx
> Regards,
> Greg Linwood
> SQL Server MVP
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OUziSWQ1GHA.1256@.TK2MSFTNGP04.phx.gbl...
>> 1) yes, otherwise you will end up doing table scans. With the indexes in
>> place you will scan the index which is much more efficient.
>> 2) yes, always go from most restrictive to least restrict. You probably
>> have more first names, than last names and more last names than
>> companies, hence the index should look like companyID, LastName,
>> Firstname. I would use one index but it really depends. If most of your
>> queries are like this
>> select * from tablename where companyid=1 and lastname =smith and
>> firstname=don the index should be on companyid, lastname and then first
>> name, however if you query on lastnames or firstnames you should have an
>> index on each of these columns as well.
>> Use a clustered index if you are doing a scan ie select * from tablename
>> where companyid>100, otherwise if you are only returning a few values use
>> a non clustered index.
>> HTH
>> --
>> Hilary Cotter
>> Director of Text Mining and Database Strategy
>> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>> This posting is my own and doesn't necessarily represent RelevantNoise's
>> positions, strategies or opinions.
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> "Don Miller" <nospam@.nospam.com> wrote in message
>> news:Obu59EP1GHA.324@.TK2MSFTNGP05.phx.gbl...
>> After doing some reading, I'd like some expert opinions on choosing
>> indexes
>> for several scenarios.
>> 1) One book said that you should almost index foreign keys in a table
>> since
>> lookups are more inefficient without them. I have tables with up to ten
>> FKs
>> used mostly for lookups from/joins with the PK table (e.g. marital
>> status,
>> education level, etc.). Each of these PK lookup tables only have 3-10
>> possible values. However, pulling info from these PKs to display the
>> linked
>> items is a frequent event and many times a day a massive report
>> effectively
>> denormalizes everything for any individual customer.
>> Generally, is it worth the overhead to index foreign keys in one table
>> for
>> primary key tables that have a handful of values?
>> 2) If I have a web application for numerous companies that each have
>> their
>> own customers, and when the folks from one company are using the
>> application
>> they only see their customers one at a time or an alphabetical list (a
>> pretty common scenario I would think), what is the best way to index
>> CompanyID (a FK to a Company PK table), CustLastName, CustFirstName?
>> Is it better to have three distinct indexes? Which one should be the
>> optimal
>> for a clustered index? (I would think CompanyID since it is used for
>> almost
>> every query except for individual customers which are already indexed by
>> being a PK as CustomerID)
>> Or, should all of these be rolled into one composite index? If these are
>> rolled into a composite index, is there any value at all for still
>> having
>> distinct indexes for these three columns? Would you still need an index
>> for
>> the company FK join to the PK Company table, e.g. to display the company
>> name?
>> Even though the CompanyID is not as selective as the combination of
>> first
>> name and last name, I would think a clustered index of CompanyID,
>> CustLastName, CustFirstName would do the trick without any other
>> indices.
>> But maybe a separate index for CompanyID (for company information
>> joins)?
>> Thanks for any tips.
>>
>>
>|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:fj39g258fjaagokp4k9ci2r2flbqkpindb@.4ax.com...
> For queries like that, a clustered index no (CompanyID, CustLastName,
> CustFirstName) is best. The engine can use the index structure to find
> the first matching row in an instant; after that, it can navigate the
> logical order of the data in the clustered index to find all rows for
> the same company, and they'll already be in the order required. (It
> might help to include CompanyID as the first column in the ORDER BY
> clause.)
Actually, I tried to simplify things a little. Besides a company ID, there
are multiple regions within that company. I tried a clustered composite
index using companyID, regionID, lastname, firstname
Just looking at execution plans, I found that if I have a query like
select * from companies where companyID = ' and regionID = ' order by
lastname, firstname, or (as you suggested)
select * from companies where companyID = ' order by regionID, lastname,
firstname
there is one clustered index seek, but if I drop the regionID in the where
or order by clause, the execution plan includes a sort step in addition to
the index seek.
Hmmm. Didn't know that including *somewhere* the columns in a clustered
composite index would eliminate execution steps. Thanks for the
parenthetical.
