When to use DB Indexes

Indexes are something I don’t really think about a lot, but know that they can have dramatic impacts on performance.

A good rule of thumb is to create database indexes for everything that is referenced in the WHERE, HAVING and ORDER BY parts of your SQL queries. — Igor Šarčević

You should pretty much add indexes to all foreign keys.

An index for a certain column/columns in a database works similarly to an index in a book. Instead of scanning every page of a book for all instances of a subject, we flip to the index, which is usually alphabetized in some fashion, and find the subject in there. The subject entry points us to the relevant pages of the book. — source

Another case is for uniqueness.

For example, if a users table has a uniqueness validation for combination email and username.

(i.e. there cannot be more than one user with the same email/username combo)

Indexing the email attribute will allow our database to abort any save operation on a non-unique email, giving us a second line of defense against the wild users of your site.

add_index :table_name, [:column_name_a, :column_name_b], unique: true

source