Index creation in Ruby on Rails

Introduction

Indexes help the database find data faster, like a shortcut in a book's table of contents.

When you want to speed up searches on a column in your database table.

When you often look up records by a specific field, like user email or product ID.

When you want to enforce uniqueness so no two rows have the same value in a column.

When you join tables on certain columns frequently and want faster results.

Syntax

Ruby on Rails

add_index :table_name, :column_name, options_hash

Use add_index inside a Rails migration file.

You can add options like unique: true to prevent duplicate values.

Examples

Adds a simple index on the email column of the users table.

Ruby on Rails

add_index :users, :email

Adds a multi-column index on category_id and price in the products table.

Ruby on Rails

add_index :products, [:category_id, :price]

Adds a unique index on username to ensure no duplicates.

Ruby on Rails

add_index :users, :username, unique: true

Sample Program

This migration adds a unique index on the email column in the users table to speed up lookups and prevent duplicate emails.

Ruby on Rails

class AddIndexToUsersEmail < ActiveRecord::Migration[7.0]
  def change
    add_index :users, :email, unique: true
  end
end

Important Notes

Adding indexes speeds up queries but can slow down data inserts and updates.

Always add indexes through migrations to keep your database schema consistent.

Use unique indexes to enforce data rules like no duplicate emails.

Summary

Indexes make data searches faster by creating shortcuts.

Use add_index in Rails migrations to create indexes.

Unique indexes prevent duplicate values in a column.