What are indexes and how do they work?

Indexes are data structures that accelerate data lookup in database tables. They work like a pointer in a book.

How they work:

1. Create an index on one or more columns.
2. The database builds a separate data structure (often B-tree or B+-tree) containing values from indexed columns and pointers to corresponding rows.
3. When a query with a condition on indexed columns (e.g., WHERE column = value) is executed, the database first searches for the value in the index, which is much faster than scanning the entire table.
4. Finding the value in the index, the database uses the pointer to quickly access the row(s) in the main table.

Example of creating an index in SQL:

-- Create index on 'email' column of 'users' table
CREATE INDEX idx_users_email ON users (email);

Advantages:

• Speed up SELECT operations.
• Speed up JOIN and ORDER BY on indexed columns.

Disadvantages:

• Slower INSERT, UPDATE, DELETE operations, as index must also be updated.
• Additional disk space for storing the index.
• Not always useful for columns with low cardinality or very small tables.