What is a Database Index? How It Works and When You Need It

This article was originally published on bmf-tech.com . What is an Index? A mechanism to quickly retrieve records stored in a table. Consider a query with an O(n) problem like the following: SELECT * FROM users WHERE first_name = ‘Tom’ To improve the performance of this query, you can add an index as follows: ALTER TABLE users ADD INDEX (first_name) Advantages and Disadvantages Advantages Improved speed of data reading and retrieval Disadvantages Increased storage size Decreased write speed When creating or updating data, indexes are also added or updated simultaneously, leading to the above disadvantages. Index Patterns Standard (Applied to a single column) ALTER TABLE users ADD INDEX (first_name) Partial Index An effective pattern when you want to improve performance while suppressing storage increase. Example of applying an index to only the first 4 bytes: ALTER TABLE users ADD INDEX (first_name(4)) Multi-column Index (Also called composite or compound index) ALTER TABLE users ADD I