How to choose indexing or hashing technique? / Points to consider in choosing an index for your application / Evaluation of different structures / When to go for indexing or hashing?
How to choose an indexing or hashing technique?
No one index is best for indexing in database. It may vary for different
database applications. Hence, choosing a technique is purely based on the
database application in question that is going to use the said index. These
indexing techniques can be evaluated for the required application on the basis
of the following points;
- Access type – it is the types of access that are supported by the index. Some applications may need to find the records with a particular search key value, or a range of values. You may choose the index based on your requirement.
- Access time – it is the time to find items by a certain type of index. The time to search and find the record in question may differ for different applications for different indexes.
- For example, if your application requires finding a single record with the primary key value as the search key, then ordered index would be a best choice for you.
- Insertion time – the time consumed to insert a record in a data file and also the time required to update the index file.
- For example, let us assume that you insert a new record into your table and the search key value of your record does not exist in the index table. Then the time to insert a record would be,
- The time to insert a record into your data file + the time to insert an appropriate index entry.
- Deletion time – Same as above. The time to delete a record entry from the data file as well as the index file (if necessary). Unlike insertion, it needs to consider the time to locate (search) the record that is to be deleted as an extra time.
- Space overhead – Additional space required to handle index.
- For example, if you like to use the index to locate your records easily, you have to load the index file into your memory first. It occupies some amount of space based on the size of the index file. Also, in some cases we may need to load more than one index file based on the application requirement.