Indexes vs Table Scans

  • 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?

  • 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

  • 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

    steve@dkranch.net

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply