Search code examples
databasedatabase-performance

Database Transaction Costs with where clause

I have a database table like below.

Database Table

My purpose is to set one of the row to default with some event. That means, if use choose to make the row with ID = 5 as default, then the isDefault value for row with ID = 2 will be set to N and the same for row with ID = 5 will be updated to Y.

As I thought of, two ways to implement this. In both the ways, it needs to make two DB transactions.

  1. Set all the rows of isDefault to N and then update the row with ID = 5 to Y.
  2. Update the isDefault field to N where isDefault = Y and then update the row with ID = 5 to Y.

In both the cases, first we need to traverse the entire table to update the isDefault field to N. If the table size grows up, traversing the entire table is costly and time consuming.

I am looking for a optimized solution for this scenario. Please let me know, which one is better and why ? I will appreciate to know any other optimized solution.

Solution

  • You really do not have to traverse the entire database!

    In case 1: "Set all the rows of isDefault to N and then update the row with ID = 5 to Y.", it is definitely a bad choice. Why bother to set ALL the rows to N? The only thing we need to do is in case 2:

    "Update the isDefault field to N where isDefault = Y". In this case, we have to lock all the effected rows and set them to N . The complexity doing so is O(log n) if the index on column isDefault in a B-tree like structure, or O(1) (constant) if the index is a hash-type structure. If you do not have any index on column 'isDefault', you will have to traverse all the rows, whose terrible low efficiency is not acceptable in practice.

    Then the optimized solution is case 2, plus an index on column 'isDefault'.