July 18, 2001 at 10:27 am
How do you determine whether an index is appropriate for a field? Is there a rule of thumb to follow using the number of rows, number of distinct values, and distribution of rows within those values?
Example:
table with 100,000 rows
field1 has values of 001, 002..up to 012
distribution is fairly even between these values, i.e.
001 - 10,000 rows
002 - 10,000 rows
012 - 10,000 rows
etc.
At what point does an index become more effective than a table scan?
July 18, 2001 at 12:43 pm
It depends. Indexing is pretty complex. Even if a single index by itself doesnt have good selectivity, it may work in concert with another index. Start by indexing all your primary keys and foreign keys, and really think through where the clustered key should be. After that, the index tuning wizard is your best bet. Short of that, run the query in QA and look at the plan, change your index and try again.
Table scans not always bad. I dont care HOW it gets the answer, as long as the time is acceptable.
Andy
July 18, 2001 at 4:06 pm
I agree with Andy. PK, FK are great choices for non-clustered indexes.
Then look at which queries are run often. It doesn't do much good to index (clustered) for a report run quarterly when there is a report that runs quicker, but runs everyday and whose speed could be doubled with a different clustered index.
I try to consider the business and politial sensitivity of different queries based on who runs them. this will also impact your choices.
If this is an OLTP system, try to limit indexes to 5 or less, especially in heavily used tables.
Steve Jones
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply