Crazy helpful rake tasks for understanding what indexes to use in MongoDB (extracted from a Rails app at GitHub).

gistfile1.rb

# If you are using in a Rails app just add gem 'plucky' to Gemfile.
# Otherwise install the plucky gem and require it.
# require 'plucky'
#
# Halp.mongo_db is just a shortcut to the database I already connected
# to using the mongo ruby driver.
#
# http://api.mongodb.org/ruby/current/file.TUTORIAL.html has instructions
# for the mongo ruby driver.

namespace :indexes do
  desc "Understanding indexes and queries"
  task :instructions do
    puts "\n"
    puts "When you run a test you will see BEFORE and AFTER results with a"
    puts "description of the query followed by the cursor, query plans, and"
    puts "scanned vs found results. In between these results indexes are"
    puts "created."
    puts "\n"
    puts "The goal is to eliminate BasicCursor cursors in favor of BTreeCursor"
    puts "cursors. We do not want to use too many indexes though. Ideally just"
    puts "a few indexes will do. Every extra index slows down document creation"
    puts "for that collection."
    puts "\n"
    puts "Another indicator of whether you have created the right index is how"
    puts "large the nscanned number is. Ideally this will equal n. The closer"
    puts "you can get this to 1/1 the better."
    puts "\n"
  end

  desc "Test label queries and indexes"
  task :label_test => [:instructions, :environment] do
    collection = Halp.mongo_db['labels']
    query = Plucky::Query.new(collection)

    collection.drop_indexes

    by_name = query.where(name: 'edu.student')
    by_slug = query.where(slug: 'edu-student')
    by_open_count_sorted_by_name = query.where(:open_count => {'$gt' => 0}).sort(['name', 1])
    all_enabled_sorted_by_name = query.where(enabled: true).sort(['name', 1])

    index_info(collection)

    execute("BEFORE", "By name", by_name)
    execute("BEFORE", "By slug", by_slug)
    execute("BEFORE", "By open_count sorted by name", by_open_count_sorted_by_name)
    execute("BEFORE", "All enabled sorted by name", all_enabled_sorted_by_name)

    collection.ensure_index([['name', 1]])
    collection.ensure_index([['slug', 1]])
    collection.ensure_index([['open_count', 1], ['name', 1]])
    collection.ensure_index([['enabled', 1], ['name', 1]])

    index_info(collection)

    execute("AFTER", "By name", by_name)
    execute("AFTER", "By slug", by_slug)
    execute("AFTER", "By open_count sorted by name", by_open_count_sorted_by_name)
    execute("AFTER", "All enabled sorted by name", all_enabled_sorted_by_name)
  end

  desc "Test discussion queries and indexes"
  task :discussion_test => [:instructions, :environment] do
    raise "What you trying to do fool?" if Rails.env.production?

    collection = Halp.mongo_db['discussions']
    query = Plucky::Query.new(collection)

    collection.drop_indexes

    by_state = query.where(state: 0).sort(['last_updated_at', 1]).limit(500)
    by_multiple_states = query.where(:state => {'$in' => [0, 1]}).sort(['last_updated_at', 1]).limit(500)
    by_tag = query.where(tag: 'foo').sort(['last_updated_at', 1]).limit(500)
    by_multiple_tags = query.where(:tag => {'$in' => ['foo', 'bar']}).sort(['last_updated_at', 1]).limit(500)
    by_state_and_tag = query.where(state: 1, tag: 'foo').sort(['last_updated_at', 1]).limit(500)
    by_multiple_states_and_tags = query.where(:state => {'$in' => [0, 1]}, :tag => {'$in' => ['foo', 'bar']}).sort(['last_updated_at', 1]).limit(500)
    by_state_not_in = query.where(:state => {'$nin' => [0]}).sort(['last_updated_at', 1]).limit(500)
    by_tag_not_in = query.where(:tag => {'$nin' => ['foo']}).sort(['last_updated_at', 1]).limit(500)
    by_state_sorted_by_archived_at = query.where(state: 3).sort(['archived_at', -1]).limit(500)

    index_info(collection)

    execute("BEFORE", "By state", by_state)
    execute("BEFORE", "By multiple states", by_multiple_states)
    execute("BEFORE", "By tag", by_tag)
    execute("BEFORE", "By multiple tags", by_multiple_tags)
    execute("BEFORE", "By state and tag", by_state_and_tag)
    execute("BEFORE", "By multiple states and tags", by_multiple_states_and_tags)
    execute("BEFORE", "By state not in", by_state_not_in)
    execute("BEFORE", "By tag not in", by_tag_not_in)
    execute("BEFORE", "By state sorted by archived_at", by_state_sorted_by_archived_at)

    collection.ensure_index([['state', 1], ['tag', 1], ['last_updated_at', 1]])
    collection.ensure_index([['tag', 1], ['state', 1], ['last_updated_at', 1]])
    collection.ensure_index([['state', 1], ['archived_at', -1]])

    index_info(collection)

    execute("AFTER", "By state", by_state)
    execute("AFTER", "By multiple states", by_multiple_states)
    execute("AFTER", "By tag", by_tag)
    execute("AFTER", "By multiple tags", by_multiple_tags)
    execute("AFTER", "By state and tag", by_state_and_tag)
    execute("AFTER", "By multiple states and tags", by_multiple_states_and_tags)
    execute("AFTER", "By state not in", by_state_not_in)
    execute("AFTER", "By tag not in", by_tag_not_in)
    execute("AFTER", "By state sorted by archived_at", by_state_sorted_by_archived_at)
  end

  def index_info(collection)
    puts "Index Information"
    puts collection.index_information
    puts "\n"
  end

  def drop_index(collection, name)
    begin
      collection.drop_index(name)
    rescue
    end
  end

  def execute(prefix, name, query)
    puts "#{prefix}: #{name}"
    result = query.explain
    puts result["cursor"]
    puts result["allPlans"]
    puts "nscanned: #{result['nscanned']} n: #{result['n']}"
    puts "\n"
  end
end