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 
 (

coneybeare

Using index_logs_on_cloud_log_id_and_deleted_at index: 71.417871316 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 `cloud_logs` `cloud_logs_logs_join` ON `cloud_logs_logs_join`.`id` = `logs`.`cloud_log_id` INNER JOIN `client_application_versions` `client_application_versions_logs` ON `client_application_versions_logs`.`id` = `cloud_logs_logs_join`.`client_application_version_id` WHERE (logs.deleted_at IS NULL) AND (client_applications.account_id = 3) AND (client_applications.id = 5) AND (client_application_versions.id = 15) ORDER BY timestamp DESC LIMIT 100 OFFSET 0
+----+-------------+----------------------------------+--------+----------------------------------------------------------

coneybeare

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 `cloud_logs` `cloud_logs_logs_join` ON `cloud_logs_logs_join`.`id` = `logs`.`cloud_log_id` INNER JOIN `client_application_versions` `client_application_versions_logs` ON `client_application_versions_logs`.`id` = `cloud_logs_logs_join`.`client_application_version_id` WHERE (logs.deleted_at IS NULL) AND (client_applications.account_id = '3') AND (client_applications.id = '5') AND (client_application_versions.id = '15') ORDER BY timestamp DESC LIMIT 100 OFFSET 0
+----+-------------+----------------------------------+--------+--------------------------------------------------------------------+---------------------------------------------------+--------