Indexing

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a table is accessed. You can think of a database index like the index at the back of a book. Instead of reading the entire book to find a specific topic, you can look up the topic in the index, which will tell you the exact page numbers where it's mentioned. Similarly, when you run a query with a `WHERE` clause on an indexed column, the database can use the index to find the matching rows much faster than it would by scanning the entire table (a 'full table scan'). For example, if you frequently query your `users` table to find a user by their `email`, creating an index on the `email` column would dramatically speed up those queries. Without an index, the database would have to check the email of every single user in the table. With an index, it can go directly to the rows that match the email address. While indexes are crucial for read performance, they do have a cost. They take up storage space, and they need to be updated whenever you `INSERT`, `UPDATE`, or `DELETE` data in the indexed table, which can slightly slow down write operations. The key is to identify the columns that are most frequently used in your query filters (`WHERE` clauses) and joins, and create indexes on them.