jodosha (owner)

Revisions

gist: 217194 Download_button fork
public
Public Clone URL: git://gist.github.com/217194.git
Embed All Files: show embed
pagination.rb #
raw 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

#!/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)