coneybeare

$ gem env
RubyGems Environment:
  - RUBYGEMS VERSION: 1.8.25
  - RUBY VERSION: 1.9.3 (2013-02-06 patchlevel 385) [x86_64-darwin12.2.1]
  - INSTALLATION DIRECTORY: /Users/REDACTED/.rvm/gems/ruby-1.9.3-p385@REDACTED
  - RUBY EXECUTABLE: /Users/REDACTED/.rvm/rubies/ruby-1.9.3-p385/bin/ruby
  - EXECUTABLE DIRECTORY: /Users/REDACTED/.rvm/gems/ruby-1.9.3-p385@REDACTED/bin
  - RUBYGEMS PLATFORMS:
    - ruby
    - x86_64-darwin-12

coneybeare

GIT
  remote: git://github.com/ctran/annotate_models.git
  revision: 8bd159c7a484093fde84beaa9e6398f25ddacf09
  specs:
    annotate (2.6.0.beta1)
      activerecord (>= 2.3.0)
      rake (>= 0.8.7)

GIT
  remote: git://github.com/jumph4x/rails-canonical.git

coneybeare

>> bad_urls.count
=> 541
>> bad_urls.each{|u|puts u}
http://www.cpsc.gov/cpscpub/prerel/prhtml10/10254.html
http://www.cpsc.gov/cpscpub/prerel/prhtml10/10239.html
http://www.cpsc.gov/cpscpub/prerel/prhtml10/10728.html
http://www.cpsc.gov/cpscpub/prerel/prhtml10/10047.html
http://www.cpsc.gov/cpscpub/prerel/prhtml10/10040.html
http://www.cpsc.gov/cpscpub/prerel/prhtml09/09310.html
http://www.cpsc.gov/cpscpub/prerel/prhtml09/09227.html

coneybeare

EXPLAIN for: SELECT
  l.*
FROM
  logs as l
  INNER JOIN cloud_logs as cl1
    ON
      cl1.id = l.cloud_log_id
--  INNER JOIN cloud_logs as cl2
--    ON
--      cl2.id = l.cloud_log_id

coneybeare

EXPLAIN for: SELECT
  l.*
FROM
  logs as l
  INNER JOIN cloud_logs as cl1
    ON
      cl1.id = l.cloud_log_id
--  INNER JOIN cloud_logs as cl2
--    ON
--      cl2.id = l.cloud_log_id

coneybeare

CLIENT_APPLICATIONS:
  PRIMARY KEY  (`id`),
  UNIQUE KEY `index_client_applications_on_key` (`key`),
  KEY `index_client_applications_on_account_id` (`account_id`),
  KEY `index_client_applications_on_deleted_at` (`deleted_at`),
  KEY `index_client_applications_on_public_key` (`public_key`)

CLIENT_APPLICATION_VERSIONS:
  PRIMARY KEY  (`id`),
  KEY `index_client_application_versions_on_client_application_id` (`client_application_id`),

coneybeare

EXPLAIN for: SELECT
  l.*
FROM
  logs as l
  INNER JOIN cloud_logs as cl1
    ON
      cl1.id = l.cloud_log_id
--  INNER JOIN cloud_logs as cl2
--    ON
--      cl2.id = l.cloud_log_id

coneybeare

EXPLAIN for: SELECT
  l.*
FROM
  logs as l
  INNER JOIN cloud_logs as cl1
    ON
      cl1.id = l.cloud_log_id
    INNER JOIN cloud_logs as cl2
      ON
        cl2.id = l.cloud_log_id

coneybeare

ClientApplication.find_by_account_id_and_public_key(1, 'p0kZudG0').versions.select{|cav| cav.public_key = '0HgoJRyE'}.first.logs.page(2)
  ClientApplication Load (165.9ms)  SELECT `client_applications`.* FROM `client_applications` WHERE `client_applications`.`account_id` = 1 AND `client_applications`.`public_key` = 'p0kZudG0' AND (client_applications.deleted_at IS NULL) ORDER BY client_applications.id LIMIT 1
  ClientApplicationVersion Load (105.1ms)  SELECT `client_application_versions`.* FROM `client_application_versions` WHERE `client_application_versions`.`client_application_id` = 3 AND (client_application_versions.deleted_at IS NULL) ORDER BY client_application_versions.created_at DESC, client_application_versions.id DESC
  Log Load (57295.0ms)  SELECT `logs`.* FROM `logs` INNER JOIN `cloud_logs` ON `logs`.`cloud_log_id` = `cloud_logs`.`id` WHERE `cloud_logs`.`client_application_version_id` = 49 AND (logs.deleted_at IS NULL) AND (cloud_logs.deleted_at IS NULL) ORDER BY logs.timestamp DESC, cloud_logs.rec

coneybeare

Using subquery to get id's: 14.351424055 seconds

EXPLAIN for: 
SELECT logs.* FROM logs
 INNER JOIN cloud_logs                  ON cloud_logs.id                    = logs.cloud_log_id 
 INNER JOIN client_application_versions ON client_application_versions.id   = cloud_logs.client_application_version_id 
 INNER JOIN client_applications         ON client_applications.id           = client_application_versions.client_application_id 
 INNER JOIN 
 (