Do you need indexes on all the columns? 🤔#tech #technical#reels #softwaredeveloper #college

If you ask a junior engineer to speed up database queries on this order table, they will probably say just add indexes on all the column. But if you ask the same thing to a senior engineer, they will say that's a terrible idea. And let me tell you why. Every time you insert, update, or delete a row, the database does not just modify the table. It also has to update every single index on that table. So in this example, if your order table gets thousands of new orders every minute, and if you have indexed all the 12 columns, then every new entry means doing one write to the table plus 12 additional write to update each index. That's 13 write operation for every single order. Now multiply that with thousands of orders per minute, and your database is choking. Not because of your queries, but because of your indexes. Now here's what a senior engineer would do. They would look at the actual queries the application is running and only index the columns that appear in where clauses, join conditions, and order by statements, not on all the columns. So here is a simple rule. If you have a read-heavy table with few writes, then more indexes are fine. They speed up your queries with a little cost. But if you have a write-heavy table with frequent insert and updates, be selective. Every unnecessary index can slow down your writes. And follow for more.