Database Management Systems (DBMS) Question Bank 02

List the important considerations by the database administrator while choosing the indexes for a database.

Question:

List the important considerations by the database administrator while choosing the indexes for a database.

Answer:

Database Administrator (DBA) has to consider these points while choosing the indexes;

• Determine the workload – The queries that are to be executed on the database, and their frequencies can be referred as the workload. DBA has to understand the workload in a database.

• Identify most frequent queries – creating indexes on most frequently executed queries will help in speed-up execution. Indexes on such queries are really useful and help in optimizing the performance.

• Identify clustered and non-clustered indexes – a table can have only one clustered index (A clustered index defines the order in which data is physically stored in a table), hence it is very important to choose the attribute (or attributes) on which the index has to be created.

• Calculate the trade-off between slowing down updates, consuming space, and speeding-up queries to select the right index.

************************