When is the perfect time for us to create indexes?
Before you actually thinking of creating indexes for a database table, you should ask yourself how you will use the table.
Most common operation that us developers use are insert, update, delete records and retrieve records.
If you often use these 3 command:
- Insert
- Update
- Delete
I would suggest fewer indexes associated with the table as it will help u a lot in term of better performance.
Now u ask WHY?
Driver must maintain the indexes as well as the database tables, thus slowing down the performance of record inserts, updates, and deletes.
It may be more efficient to drop all indexes before modifying a large number of records, and re-create the indexes after the modifications.
If you often Retrieve records:
Look further to define the criteria for retrieving records and create indexes to improve the performance of these retrievals.
Example scenario :
Assume you have an employee database table and you will retrieve records based on employee name, department, or hire date. You would create three indexes—one on the DEPT field, one on the HIRE_DATE field, and one on the LAST_NAME field.
Or perhaps, for the retrievals based on the name field, you would want an index that concatenates the LAST_NAME and the FIRST_NAME fields.
EmoticonEmoticon