Database Indexing Mistakes

Many developers are rightly nervous when dealing with databases. Not only is the database absolutely critical to proper functioning of the system, but its inner workings can often be very mysterious to people who aren’t constantly mucking around with it. And worse still, the database and the data within it are often the most valuable part of your application. Making things even more difficult, changes to the database can be difficult or impossible to roll back, especially if data was altered or destroyed by the changes. Small database changes can also have insidious, unpredictable impacts months later, either when the system comes under load or when a large number of records have been added. Such behaviors mean that unless a developer is very comfortable with databases, that they will tend to do things that seem to work well for other people. While this does mean that good ideas and good habits CAN spread around, anyone who has seen how rumors spread in a population probably knows that all kinds of bad ideas WILL spread further. It’s an environment that lends itself to cargo cult thinking. As a result, many developers have misconceptions about databases. These misconceptions sound great in theory, but they aren’t so great in practice. In fact, many of these misconceptions can really cause major database performance issues. There are a lot of common misconceptions about how database indexes work. If you haven’t spent a lot of time studying how databases work at a low level (or thinking about how they MIGHT work from a data structure perspective), it’s very easy to end up with a mental model of things that has no relationship to reality. As a result, many developers have misconceptions about indexes that can seriously harm performance. If nothing else, a lot of these ideas can waste a lot of developer time and cause frustration for team members. And worst of all, these misconceptions often don’t cause problems until the worst possible moment, when the system is under load. However, if you can avoid these misconceptions, you’ll tend to write better SQL code. If you got nothing else from this episode, it’s important to understand one thing – measure your results when making database changes, rather than making assumptions about what those results will be. Links Join Us On Patreon Level Up Financial Planning