www.sum-it.nl > Courses
> DB Design course > Method > 3. Technical optimisation > Steps > Add indexes		Previous Previous Next Next
Nederlands · Search...

Add indexes

Benefit of an index

For large tables an index can improve performance. An index can
  1. make rows unique using single or compound keys
  2. speed up search actions with
    • data or key values
    • foreign keys, the one you marked with an asterisk in TNF. This kind of indexes will especially speed up joins.
    • combination of keys, foreign keys and data values.
  3. sort a table

How does an index work?

An index refers to pages within a table
    An index
  • is sorted by key values, (that need not be the same as those of the table)
  • is small, has just a few columns of the table.
  • refers for a key value to the right block within the table.
  • speeds up reading a row, when you know the right search arguments.

Tips

 Introduction 1. Scope 2. Logical Design
3. Technical Optimisation
Goal Optimisation steps Use ranges Denormalise Combine tables Store derived data
Add indexes
Sort 4. Implementation End
  • Put the most unique data element first in the index, the element that has the biggest veriety of values. The index will find the correct page faster. It's better to have postcode-country rather than country-postcode.
  • Keep indexes small. It's better to have an index on just postcode, rather than postcode-country. The smaller the index, the better the response time.
  • For high frequency functions (thousands of times per day) it can be wise to have a very large index, so the system does not even need the table for the read function.
  • For small tables an index is disadvantageous. For any function the system would be better off by scanning the whole table. An index would only slow down.
  • It's no use to create an index for a function that would go through the whole table anyway. In that case it is more efficient to scan through the entire table without accessing the index.

    You'll also save the extra index processing at add, modify and delete.

  • Make a cost-benefit analysis for every index. Does a faster read function outweight a slower add/modify/delete?

    Take the frequency and load of the functions into account. A light read function with a frequency function of 10,000 times per day is more important that a heavy delete function that runs only once a month.

Attention An index slows down additions, modifications and deletes. It's not just the table that needs an update, but the index as well.

So, preferably, add an index for values that are often used for a search, but that do not change much. An index on bank account number is better than one on balance.

Next: Sort