Optimize Queries

Improve query performance with indexing, eager loading, and query optimization techniques.

Prerequisites

• Working CQL application

• Understanding of your data access patterns

Add Database Indexes

Indexes dramatically speed up queries on frequently searched columns.

Single Column Index

class AddIndexes < CQL::Migration(1)
  def up
    add_index :users, :email
    add_index :posts, :user_id
    add_index :posts, :published_at
  end

  def down
    remove_index :users, :email
    remove_index :posts, :user_id
    remove_index :posts, :published_at
  end
end

Composite Index

For queries filtering on multiple columns:

# For queries like: WHERE user_id = ? AND status = ?
add_index :orders, [:user_id, :status]

Unique Index

Enforce uniqueness and speed up lookups:

add_index :users, :email, unique: true

Use Select to Limit Columns

Only fetch columns you need:

# Instead of
users = User.all

# Use
users = User.select(:id, :name, :email).all

Use Pluck for Single Values

When you only need specific columns without model objects:

# Returns array of emails
emails = User.where(active: true).pluck(:email)

# Returns array of [id, name] tuples
data = User.select(:id, :name).pluck(:id, :name)

Limit Result Sets

Always paginate or limit large queries:

# Pagination
users = User.limit(20).offset(40).all

# Or use cursor pagination for large datasets
users = User.where { id > last_id }.limit(20).all

Use Count Instead of Size

For counting records without loading them:

# Efficient - runs COUNT query
count = User.where(active: true).count

# Inefficient - loads all records
count = User.where(active: true).all.size

Batch Processing

Process large datasets in batches:

User.where(needs_update: true).each_batch(1000) do |batch|
  batch.each do |user|
    user.update!(processed: true)
  end
end

Use Exists Instead of Count

When checking for presence:

# Efficient - stops at first match
if User.where(email: email).exists?
  # ...
end

# Inefficient - counts all matches
if User.where(email: email).count > 0
  # ...
end

Optimize Ordering

Ensure ordered columns are indexed:

# Add index for common ordering
add_index :posts, :created_at

# Query uses index
Post.order(created_at: :desc).limit(10).all

Analyze Slow Queries

Log queries to find bottlenecks:

MyDB.on_query do |sql, duration|
  if duration > 100.milliseconds
    Log.warn { "Slow query (#{duration}ms): #{sql}" }
  end
end

Verify Optimization

Check query execution plans:

result = MyDB.exec("EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[e-mail]'")
puts result.first

Look for:

• Index scans (good) vs sequential scans (bad)

• Low row estimates

• Efficient join methods

See Also

• Avoid N+1 Queries

• Create Indexes

• Monitor Performance