I was asked a question regarding the need to add an index on a database table with less no. of records. Say small master tables. I believe its essential to put an index on appropriate columns for such small tables as well. Here is the rationale behind it -
1. In my experience one of the top root causes of slow transaction is repetitive queries. It can so happen that smaller tables are queried many times which then increases the record scan that many times.
2. The db optimizer chooses an execution plan basis Selectivity index of columns, indexes, PK, data type and some other constraints of the tables involved in the query. I have come across few instances where large queries are tuned just by adding indexes in some master tables the query joins with.
1. In my experience one of the top root causes of slow transaction is repetitive queries. It can so happen that smaller tables are queried many times which then increases the record scan that many times.
2. The db optimizer chooses an execution plan basis Selectivity index of columns, indexes, PK, data type and some other constraints of the tables involved in the query. I have come across few instances where large queries are tuned just by adding indexes in some master tables the query joins with.
