Benefit of an index
can improve performance.
An index can
- make rows unique using single or
- speed up search actions with
- data or key values
- foreign keys, the one you marked with an asterisk
This kind of indexes will especially speed up joins.
- combination of keys, foreign keys and data values.
- sort a table
How does an index work?
- 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.
- 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.
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.