pagination.rb

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

# Given 10,000 records in `venues`, I want to fetch the last records, sorted by descending calculated `distance` from a given geographic location.

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

Benchmark.bm(30) do |b|
  b.report "with offset" do
    TIMES.times do |i|
      result_set = connection.query(%(SELECT *, (ACOS(least(1,COS(0.731292991492209)*COS(0.217872941184956)*COS(RADIANS(venues.lat))*COS(RADIANS(venues.lng))+
       COS(0.731292991492209)*SIN(0.217872941184956)*COS(RADIANS(venues.lat))*SIN(RADIANS(venues.lng))+
       SIN(0.731292991492209)*SIN(RADIANS(venues.lat))))*3963.19)
       AS distance FROM `venues` WHERE (((venues.lat>41.7554321471464 AND venues.lat<42.0445718528536 AND venues.lng>12.2889673414887 AND venues.lng<12.6774326585113)) AND ( (ACOS(least(1,COS(0.731292991492209)*COS(0.217872941184956)*COS(RADIANS(venues.lat))*COS(RADIANS(venues.lng))+
       COS(0.731292991492209)*SIN(0.217872941184956)*COS(RADIANS(venues.lat))*SIN(RADIANS(venues.lng))+
       SIN(0.731292991492209)*SIN(RADIANS(venues.lat))))*3963.19)
       <= 10)) ORDER BY (ACOS(least(1,COS(0.731292991492209)*COS(0.217872941184956)*COS(RADIANS(venues.lat))*COS(RADIANS(venues.lng))+
       COS(0.731292991492209)*SIN(0.217872941184956)*COS(RADIANS(venues.lat))*SIN(RADIANS(venues.lng))+
       SIN(0.731292991492209)*SIN(RADIANS(venues.lat))))*3963.19)
       ASC LIMIT 9990, 30))
      result_set.free
    end
  end

  b.report "without offset" do
    TIMES.times do |i|
      result_set = connection.query(%(SELECT *, (ACOS(least(1,COS(0.731292991492209)*COS(0.217872941184956)*COS(RADIANS(venues.lat))*COS(RADIANS(venues.lng))+
       COS(0.731292991492209)*SIN(0.217872941184956)*COS(RADIANS(venues.lat))*SIN(RADIANS(venues.lng))+
       SIN(0.731292991492209)*SIN(RADIANS(venues.lat))))*3963.19)
       AS distance FROM `venues` WHERE (((BINARY( (ACOS(least(1,COS(0.731292991492209)*COS(0.217872941184956)*COS(RADIANS(venues.lat))*COS(RADIANS(venues.lng))+
       COS(0.731292991492209)*SIN(0.217872941184956)*COS(RADIANS(venues.lat))*SIN(RADIANS(venues.lng))+
       SIN(0.731292991492209)*SIN(RADIANS(venues.lat))))*3963.19)
      ) > BINARY('4.30256219159068')) AND (venues.lat>41.7554321471464 AND venues.lat<42.0445718528536 AND venues.lng>12.2889673414887 AND venues.lng<12.6774326585113)) AND ( (ACOS(least(1,COS(0.731292991492209)*COS(0.217872941184956)*COS(RADIANS(venues.lat))*COS(RADIANS(venues.lng))+
       COS(0.731292991492209)*SIN(0.217872941184956)*COS(RADIANS(venues.lat))*SIN(RADIANS(venues.lng))+
       SIN(0.731292991492209)*SIN(RADIANS(venues.lat))))*3963.19)
       <= 10)) ORDER BY (ACOS(least(1,COS(0.731292991492209)*COS(0.217872941184956)*COS(RADIANS(venues.lat))*COS(RADIANS(venues.lng))+
       COS(0.731292991492209)*SIN(0.217872941184956)*COS(RADIANS(venues.lat))*SIN(RADIANS(venues.lng))+
       SIN(0.731292991492209)*SIN(RADIANS(venues.lat))))*3963.19)
       ASC, id ASC LIMIT 30))
      result_set.free
    end
  end
end

__END__
# 1 time
                                    user     system      total        real
with offset                     0.000000   0.000000   0.000000 (  0.079092)
without offset                  0.000000   0.000000   0.000000 (  0.032674)

# 10 times
                                    user     system      total        real
with offset                     0.010000   0.000000   0.010000 (  0.782092)
without offset                  0.000000   0.000000   0.000000 (  0.336098)

# 100 times
                                    user     system      total        real
with offset                     0.010000   0.000000   0.010000 (  7.787935)
without offset                  0.000000   0.010000   0.010000 (  3.276523)

# 1,000 times
                                    user     system      total        real
with offset                     0.040000   0.040000   0.080000 ( 78.152843)
without offset                  0.040000   0.030000   0.070000 ( 33.821875)

# 10,000 times
                                    user     system      total        real
with offset                     0.450000   0.360000   0.810000 (781.790011)
without offset                  0.410000   0.320000   0.730000 (338.232946)