Should we create index of a column if write operation becomes slower because of creating index?

I know, we create indexes on column to make read queries faster.

I got to know that, once we create index on a column, write query becomes slower because when insert happens it updates main table as well as index is updated.

I have a table quote with columns like entity_id, increment_id, grand_totaland few more columns.

My target column is increment_id which holds unique values. Once the data is inserted, I also have a read query like select * from quote where increment_id='123'

My question is should I create index for increment_id column? quote table contains 100K+ rows at the moment and will grow going forward.

If you don't create the index, then the query you show will do a table-scan on every query. That will make the select query have very bad performance.

It's up to you to decide if you want to optimize for the inserts, or for the select. If you absolutely need inserts to be as quick as possible, and you don't care if the select takes many seconds, then you can skip the index.

For most applications, it's a good tradeoff to create the index that helps your specific read query. It's very expensive to do table-scans, and it gets worse as the number of rows increases. The overhead of inserting into a table with an index isn't so bad.

Thinking of the algorithmic complexity, a table-scan is O(n), whereas inserting into a table with an index is just two writes to B-tree data structures (the table is a clustered index, which counts as one B-tree write), so you have 2x O(log n).

The table-scan performance worse much faster as the table grows than the index writes.