How is an index used in a large table?

An index in a large table significantly speeds up data retrieval operations (SELECT), as well as modification operations (UPDATE, DELETE) when using a WHERE condition with indexed columns. Without an index, the database must perform a full table scan, which is very slow on large datasets.

When using an index, the database first accesses the index structure (usually a B-tree), which is much smaller than the table itself and contains ordered references to data rows. By finding the necessary references in the index, the database quickly gains direct access to the required table rows.

Important:

• Indexes occupy space on disk.
• Creating and maintaining indexes during insert (INSERT) and update (UPDATE) operations requires additional resources.
• Too many indexes or incorrectly chosen indexes can slow down write operations.

Example of creating an index in Ruby on Rails:

# Creating an index on the 'email' column in the 'users' table
add_index :users, :email

# Creating a unique index on the 'username' column
add_index :users, :username, unique: true

# Creating a composite index on 'user_id' and 'product_id' columns
add_index :orders, [:user_id, :product_id]

Example of using an index in SQL (executed by the database):

-- Query that will use the index on the email column
SELECT * FROM users WHERE email = 'test@example.com';