New Index creation can benefit from existing Index



  • I was wondering if a new index and consequentially its creation might benefit from an existing index. It is something just out of curiosity but extremely helpful in the case when you have to create a combined index where the first column already has one.

    I give you an example:

    • I have a database table with those columns customer_id, department.
    • I have an index on the customer_id column
    • I need a new combined index customer_id-department

    Given the combined index customer_id-department will cover the existing one I can delete the customer_id_index. Is it better to first remove it and then create the new one or given the fact the new one it is a combined one with an existing index it might benefit from it?

    Then create the new combined one and after that delete the single index?



  • The creation of the composite index will not use the existing index, you have to obtain the additional column from the table and sort with the other column. It will (almost always) be faster to read the table with a bulky scan and sort by the two columns.

    That said, dropping the existing index first means that the queries that were using it are no longer able to until your new index is ready. This means that during the build (and afterwards while these queries are still running), you will be contending with a lot more IO activity and probably end user complaints.

    Take note that your new index will be bigger, this means that some queries may end up being slower. This difference may not be noticeable or important, but on the extreme end, if you have queries that require reading your old index entirely, they will now have to read your new bigger index entirely. It is a balancing act with the queries you plan on improving with the additional column. There is an option to keep both indexes, which means additional storage usage and additional impact to DML activity on the table.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2