Indexes are there to simplify our work when searching for data: they speed up SELECT queries at the expense of slowing down other kinds of queries like DELETEs, UPDATEs, and INSERT s instead. However, as awesome as indexes might be, they also need a lot of work to get right — in this blog, we will tell you how you should go about deriving ideal indexes for your database. The majority of the examples in this article will focus on MySQL: however, the concept is the same for all major database management systems available on the market today.
What Are Indexes?
If you are familiar with database structures, great — because that’s essentially what indexes are! Indexes are database structures that can be used to quickly find rows having specific column values. At the expense of taking up disk space and time if your tables are big and you find yourself adding indexes on top of them, indexes allow databases to skip reading through entire tables and instead, only scan relevant rows which means that databases have less data to scan through.