pagination.rb

#!/usr/bin/env ruby -w
require "rubygems"
require "mysql"
require "benchmark"

# Given 100,000 records in `venues`, I want to fetch the last thirty records, performing and ascending sort on indexed `name` column.

TIMES = 10
connection = Mysql.real_connect("localhost", "username", "password", "database")

Benchmark.bm(30) do |b|
  b.report "with offset" do
    TIMES.times do |i|
      connection.query(%(SELECT * FROM `venues` ORDER by `name` ASC LIMIT 99970, 30)).free
    end
  end

  b.report "without offset" do
    TIMES.times do |i|
      connection.query(%(SELECT * FROM `venues` WHERE `name` >= 'Voluptatum Ut' ORDER BY `name` ASC, id ASC LIMIT 30)).free
    end
  end
end

__END__
# 1 time
                                    user     system      total        real
with offset                     0.000000   0.000000   0.000000 ( 10.629788)
without offset                  0.000000   0.000000   0.000000 (  0.086164)

# 10 times
                                    user     system      total        real
with offset                     0.000000   0.000000   0.000000 (106.998109)
without offset                  0.000000   0.000000   0.000000 (  0.909263)