Indexing is one of the most efficient ways to obtain data from a database. Index technology can be used to solve 95% of database performance problems.

Indexing is one of the most efficient ways to obtain data from a database. Index technology can be used to solve 95% of database performance problems.

1. Do not index frequently used small tables

Do not set any keys for small data tables. Do not do this if they are often inserted or deleted. The index maintenance for these insert and delete operations may consume more time than the scan tablespace.

2. Do not select the social security number (SSN) or ID Number (ID) as the key

Never use SSN or ID as the database key. In addition to privacy, SSN or ID must be manually entered. Never use a manually entered key as the primary key, because once you enter an error, the only thing you can do is Delete the entire record and start from scratch.

3. Do not use user keys

When determining the fields used as the table key, you must be careful with the fields to be edited. Generally, do not select an Editable field as the key. This will force you to take the following two measures:

4. Do not index Memo/Notes fields or large text fields (many characters)

This will occupy a large amount of database space for your indexes.

5. Use the primary key generated by the system

If you always use the key generated by the system as the primary key when designing the database, you actually control the index integrity of the database. In this way, the database and non-manual mechanism effectively control access to each row of stored data.

Using the system-generated key as the primary key has another advantage: When you have a consistent key structure, it is easy to find logical defects.