Slapping Secondary Indexes on Random Fields is Silently Killing Your Database.

Why do we add indexes to our SQL fields? To make the search faster, right? But do you know it has a massive downside? Writes become slower, forcing developers to be strategic about which fields should be labelled as secondary indexes. What is the magic behind indexes? Let's say we have a table of books called “book” with fields (id, title, author, pub_date, isbn). SELECT * FROM book WHERE author=”C.S. Lewis” This causes the database engine to search through the Heap (a physical unstructured file where all data in the database actually lives). Let's say we have a total of 1 million rows in the table and only 20 Lewis books. The database engine would scan through all the 1 million rows, even when it had gotten the total of 20 Lewis books, just to be sure it didn’t miss any. This is a Full Table Scan with O(n) complexity. It is brutally slow. THE FIX When you add the field “author” as a secondary index CREATE INDEX idx_author ON book(author); The database engine creates a separate, highly