Indexes

An Index is a special lookup table that the database search engine can use to speed up data retrieval. Think of it like the index at the back of a book: instead of scanning every page to find a topic, you look up the topic in the index, which tells you the exact page numbers. Similarly, a database index provides a fast lookup path to the rows in a table. Indexes are created on one or more columns of a table. When you create an index, the database system creates a data structure (commonly a B-tree) that stores the column values and a pointer to the corresponding row's physical location on disk. When you run a query with a `WHERE` clause that filters on an indexed column, the database can use the index to quickly find the matching rows instead of performing a full table scan (reading every single row). While indexes dramatically improve the speed of `SELECT` queries and `WHERE` clauses, they are not without cost. They take up storage space, and they must be updated whenever data is inserted, updated, or deleted in the table. This means that indexes can slow down data modification operations (`INSERT`, `UPDATE`, `DELETE`). Therefore, the key is to create indexes strategically on columns that are frequently used in `WHERE` clauses or join conditions, particularly on large tables where full scans would be very slow.