MySQL Indexing for Rails Developers

December 31, 2025

Slow database queries kill Rails applications. The fix is usually straightforward: add the right index. But knowing which index to add and why it works separates performant applications from sluggish ones.

This guide covers practical MySQL indexing strategies for Rails developers—from basic single-column indexes to composite indexes that handle complex queries.

Understanding Index Basics

An index is a data structure that helps MySQL find rows without scanning the entire table. Think of it like a book's index: instead of reading every page to find "authentication," flip to the index and get the exact page number.

Without an index, MySQL performs a full table scan. On a table with 100,000 rows, this means examining every single row. With the right index, MySQL jumps directly to matching rows.

Rails migrations make creating indexes straightforward:

# db/migrate/20251231000001_add_index_to_orders_user_id.rb
class AddIndexToOrdersUserId < ActiveRecord::Migration[8.0]
  def change
    add_index :orders, :user_id
  end
end

This single line creates a B-tree index on the user_id column. Queries filtering by user_id now use the index instead of scanning every order.

Composite Indexes for Multiple Columns

Real applications rarely filter by a single column. Consider an e-commerce dashboard showing orders for a specific user within a date range:

# app/models/order.rb
class Order < ApplicationRecord
  belongs_to :user
  
  scope :for_user_in_range, ->(user_id, start_date, end_date) {
    where(user_id: user_id)
      .where(created_at: start_date..end_date)
      .order(created_at: :desc)
  }
end

A single-column index on user_id helps, but MySQL still scans all orders for that user to filter by date. A composite index handles both conditions efficiently:

# db/migrate/20251231000002_add_composite_index_to_orders.rb
class AddCompositeIndexToOrders < ActiveRecord::Migration[8.0]
  def change
    add_index :orders, [:user_id, :created_at], name: 'index_orders_on_user_and_date'
  end
end

Column order in composite indexes matters significantly. MySQL uses indexes left-to-right. This index works for:

  • Queries filtering by user_id alone
  • Queries filtering by user_id AND created_at
  • Queries filtering by user_id and sorting by created_at

It does not help queries filtering only by created_at. The leftmost column must be present in the query's WHERE clause.

Using EXPLAIN to Verify Index Usage

Guessing whether MySQL uses an index wastes time. The EXPLAIN command reveals exactly what happens during query execution.

Rails provides a convenient way to check query plans:

# In Rails console
Order.where(user_id: 42).where(created_at: 1.month.ago..Time.current).explain

# Output shows:
# EXPLAIN SELECT `orders`.* FROM `orders` 
# WHERE `orders`.`user_id` = 42 
# AND `orders`.`created_at` BETWEEN '2025-12-01' AND '2025-12-31'
#
# +----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
# | id | select_type | table  | type | possible_keys             | key                       | key_len | ref   | rows | Extra       |
# +----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
# |  1 | SIMPLE      | orders | range| index_orders_on_user_and_date | index_orders_on_user_and_date | 13    | NULL  |   47 | Using where |
# +----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+

Key columns to examine:

  • type: "range" or "ref" indicates index usage; "ALL" means full table scan
  • key: Shows which index MySQL selected
  • rows: Estimated rows to examine (lower is better)

Covering Indexes for Read-Heavy Queries

A covering index contains all columns needed to satisfy a query. MySQL reads everything from the index without touching the table data, dramatically speeding up reads.

Consider a reporting query that only needs specific columns:

# app/models/order.rb
class Order < ApplicationRecord
  scope :daily_totals_for_user, ->(user_id) {
    where(user_id: user_id)
      .group("DATE(created_at)")
      .select("DATE(created_at) as order_date, SUM(total_cents) as daily_total")
  }
end

A covering index includes all referenced columns:

# db/migrate/20251231000003_add_covering_index_to_orders.rb
class AddCoveringIndexToOrders < ActiveRecord::Migration[8.0]
  def change
    add_index :orders, [:user_id, :created_at, :total_cents], 
              name: 'index_orders_covering_daily_totals'
  end
end

When EXPLAIN shows "Using index" in the Extra column, MySQL satisfies the query entirely from the index—no table access required.

Partial Indexes for Filtered Queries

MySQL 8.0 supports functional indexes, which help when queries consistently filter by a computed value or subset of data. Rails 8 handles these through custom index expressions:

# db/migrate/20251231000004_add_partial_index_for_active_orders.rb
class AddPartialIndexForActiveOrders < ActiveRecord::Migration[8.0]
  def change
    # Index only pending and processing orders
    # These statuses are queried frequently; completed orders rarely
    add_index :orders, [:status, :created_at], 
              name: 'index_orders_active_status',
              where: "status IN ('pending', 'processing')"
  end
end

Note: MySQL's partial index support differs from PostgreSQL. For MySQL, consider using generated columns with indexes as an alternative approach for complex filtering scenarios.

Common Indexing Mistakes

Over-indexing: Every index slows down INSERT, UPDATE, and DELETE operations. MySQL must update each index when data changes. Add indexes based on actual query patterns, not speculation.

Indexing low-cardinality columns: A boolean active column with only true/false values makes a poor standalone index. MySQL might ignore it entirely, preferring a full table scan. Combine low-cardinality columns with high-cardinality ones in composite indexes.

Ignoring index length for text columns: MySQL limits index key length. For VARCHAR columns, specify a prefix length when needed:

# db/migrate/20251231000005_add_index_with_length.rb
class AddIndexWithLength < ActiveRecord::Migration[8.0]
  def change
    add_index :articles, :title, length: 100, name: 'index_articles_on_title_prefix'
  end
end

Forgetting foreign key indexes: Rails does not automatically index foreign keys. Every belongs_to association benefits from an index on the foreign key column. Make this a habit in migrations:

# db/migrate/20251231000006_create_comments.rb
class CreateComments < ActiveRecord::Migration[8.0]
  def change
    create_table :comments do |t|
      t.references :post, null: false, foreign_key: true, index: true
      t.references :user, null: false, foreign_key: true, index: true
      t.text :body
      t.timestamps
    end
  end
end

Using t.references with index: true handles this automatically.

Monitoring Index Effectiveness

MySQL tracks index usage statistics. Query the performance_schema to find unused indexes:

-- Find indexes that MySQL has never used
SELECT 
  object_schema,
  object_name,
  index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema = 'your_database_name'
ORDER BY object_name;

Unused indexes waste disk space and slow down writes. Remove them confidently after verifying they serve no queries.

Summary

Effective MySQL indexing follows clear principles: index columns that appear in WHERE clauses and JOIN conditions, order composite index columns from most selective to least, and verify index usage with EXPLAIN. Start with foreign keys and frequently-queried columns, then add composite indexes as query patterns emerge.

The next step after indexing is query optimization—restructuring Active Record queries to take full advantage of available indexes. Well-designed indexes combined with efficient queries keep Rails applications responsive as data grows.

10 claps
← Back to Blog