Column indexing and unique column constraints in Rails
This is my memo on Column indexing and unique column constraints in Rails.
Column indexing
- Looking up a row by an indexed column provides O(1) access to the data.
- When creating a column in a database, it is important to consider whether we will need to find records by that column so that we can avoid a potential full-table scan.
- Any foreign key column should additionally have an index.
# # Add an index on the email column of the users table.
$ rails generate migration add_index_to_users_email
# The migration for adding an index on the email column of the users table and
# enforcing email uniqueness.
# Note: The index by itself doesn’t enforce uniqueness.
# (The option `unique: true` does.)
# https://www.railstutorial.org/book/modeling_users#code-email_uniqueness_index
class AddIndexToUsersEmail < ActiveRecord::Migration
def change
add_index( :users, :email, unique: true )
end
end
# NOTE: When you tell Rails something like `belongs_to :photo`, Rails does not
# update the database to index `user.photo_id`.
# This way, each direction of our user and photo lookup is fast and easy.
def up
add_index( :users, :username )
add_index( :users, :photo_id )
end