db_test.md

require 'benchmark'

def random_arr(n)
  sports = ['soccer', 'hockey', 'basketball']
  type = ['players', 'teams']
  arr = []
  n.times do |i|
    arr << "/#{sports.sample}/#{type.sample}/#{rand(100)}"
  end
  arr
end

# pass query to this
def test_query(n, array_size)
  n.times do |i|
    yield(random_arr(array_size))
  end
end

def query_1(list)
  TimelineEvent.select('MAX(id) AS max_id, MIN(id) AS min_id, uri, MAX(updated_at) AS updated_at').where(resource_uri: list, language: 'en').group(:uri).order('max_id DESC').map {}
end

def query_2(list)
    TimelineEvent.select('id, uri, resource_uri, updated_at').where(resource_uri: list, language: 'en').group([:uri, :resource_uri]).order('id DESC').map {}
end

# Running 10 times with 30 random subscriptions
Benchmark.measure { test_query(10, 30) { |arr| query_1(arr) } }
Benchmark.measure { test_query(10, 30) { |arr| query_2(arr) } }

SELECT MAX(t.id) AS max_id, MIN(t.id) AS min_id,
       t.uri, MAX(t.updated_at) AS updated_at
FROM (
      SELECT MAX(id) as id, uri, resource_uri, MAX(updated_at) as updated_at
      FROM timeline_events
      WHERE timeline_events.resource_uri IN (?)
      GROUP BY resource_uri, uri
      ORDER BY id DESC LIMIT 5400   
) as t
GROUP BY t.uri ORDER BY max_id DESC LIMIT 20;

even better!

SELECT MAX(t.id) AS max_id, MIN(t.id) AS min_id,
       t.uri, MAX(t.updated_at) AS updated_at
FROM (
      SELECT id, uri, resource_uri, updated_at
      FROM timeline_events
      WHERE timeline_events.resource_uri IN (?)
      ORDER BY id DESC LIMIT 5400
) as t
GROUP BY t.uri ORDER BY max_id DESC LIMIT 20;

def raw_query(params)
  query = ActiveRecord::Base.send(:sanitize_sql_array, params)
  ActiveRecord::Base.connection.execute(query).to_a
end

SELECT MAX(t.id) AS max_id, MIN(t.id) AS min_id,
       t.uri, MAX(t.updated_at) AS updated_at
FROM (
      SELECT id, uri, resource_uri, updated_at
      FROM timeline_events
      WHERE timeline_events.resource_uri IN (?)
      AND id < ?
      ORDER BY id DESC LIMIT 5400
) as t
GROUP BY t.uri ORDER BY max_id 
DESC LIMIT 20;

def remote_resource_fetcher
  @remote_resource_fetcher ||= begin
    headers = {}.tap do |result|
      api_version = 'x-Api-Version'
      result['X-Api-Version'] = api_version if api_version.present?
    end

    TimelineEventsHydrator::ResourceFetcher.new(headers)
  end
end

def render_timeline_events(resource_uris, params)
  query_result = PaginatedTimelineEventsQuery.execute(resource_uris,params)

  hydrated_timeline_events = TimelineEventsHydrator.hydrate(
    query_result.timeline_events,
    remote_resource_fetcher
  )

  {
    cursors: query_result.cursors,
    infinite_scroll_id: query_result.infinite_scroll_id,
    results: hydrated_timeline_events
  }
end