username

[adnjoo]

79

TODO

• [x] migration
  • [x] unique index
• [x] controller
• [x] views
• [x] check prod db

[adnjoo]

An index in a database is a special data structure that improves the speed of data retrieval operations. It works similarly to an index in a book, allowing the database to quickly locate rows in a table without having to scan through every row. In relational databases, indexes can be applied to one or more columns in a table.

Why Use an Index?

Indexes are primarily used to:

• Speed up queries: When a column is indexed, the database can find rows matching specific criteria much faster, especially for large tables.
• Ensure uniqueness: In some cases, like when using UNIQUE indexes, they enforce that no two rows have the same value for a specific column.

Types of Indexes

1. Regular Index: A non-unique index that helps speed up data retrieval but allows duplicate values in the indexed column.

   Example:

   add_index :users, :email

2. Unique Index: An index that not only improves performance but also enforces that all values in the indexed column must be unique.

   Example:

   add_index :users, :username, unique: true

   This would prevent two users from having the same username in the table.

How an Index Works

When you create an index on a column (e.g., username), the database stores a copy of that column's data in a sorted order (or using a different structure like a B-tree). When you query the database, instead of scanning all rows, the database can use the index to quickly jump to the relevant rows.

Trade-offs

• Performance improvement on reads: Indexes can make reading data (e.g., SELECT queries) much faster.
• Overhead on writes: Indexes slow down INSERT, UPDATE, and DELETE operations because the index itself needs to be updated whenever data is modified.
• Storage space: Indexes take up additional space on disk.

Example in Rails

In your case, you want the username to be unique. This means you want an index to ensure fast lookups and also enforce uniqueness, which prevents duplicate usernames.

The migration:

add_index :users, :username, unique: true

creates a unique index, ensuring that no two users can have the same username while also improving query performance when looking up users by username.

Without an Index

Without an index, the database would perform a full table scan to find records. For example, when querying by username, it would have to check each row in the users table, which can be slow for large datasets.

Let me know if this clarifies things or if you need more details!