Understanding Index 2

4. Are there any negative impact of creating index ?

Indexes are not always beneficial. Careful implementation is required to exploit the benefits of index.

  • Indexes take extra space on disk.
  • Whenever any IUD(Insert/Update/Delete) operation is done on the table, the index also has to be updated instantly. This adds overhead to the IUD queries.
  • If table has too many indexes, the query plan generation may take a bit longer. This is a concern for mission critical database systems.
  • When a condition selects more rows, using index may result in bad performance.

5. Can a query use more than one index on the table to improve performance ?

Yes. When a query has more than one search conditions, more than one indexes may qualify to be used. Using these indexes may help access the target row faster. Using all the indexes may not be a good approach. Data processing systems typically have a component like Query access path planner, that decides which indexes should be used to get better performance.

6. Can index have different data compared to the table column ?

Database systems must ensure that the table and indexes are updated simultaneously. Missing to update any one of them may result in different values in table column and index. If table column and index go out of sync, the query may fetch incorrect result depending on whether the table or index is picked for accessing the row.

7. When is the index actually used by DBMS ?

Creating an index does not mean that the index will be used by the queries. Index should be used only if it would get better performance than accessing the table directly. Query access path planner should consider all candidate indexes for planning, but pick the index which will give the best performance for the search query.

Typically if the search condition selects more than 50% of the number of records, use of index is not recommended.

::Food For Thought ::

thinking

What type of data is eligible for indexing ?