gistfile1.sql

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 
 (
    SELECT logs.id 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 
    WHERE (logs.deleted_at IS NULL) 
    AND (client_applications.account_id = '3') 
    AND (client_applications.id = '5') 
    AND (client_application_versions.id = '18')
    ORDER BY timestamp DESC LIMIT 100 OFFSET 0
  ) AS PERF ON PERF.id = logs.id
+----+-------------+-----------------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+---------+-----------------------------------------------------------------------+------+---------------------------------+
| id | select_type | table                       | type   | possible_keys                                                                                                                                         | key                                                     | key_len | ref                                                                   | rows | Extra                           |
+----+-------------+-----------------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+---------+-----------------------------------------------------------------------+------+---------------------------------+
|  1 | PRIMARY     | <derived2>                  | ALL    | NULL                                                                                                                                                  | NULL                                                    | NULL    | NULL                                                                  |  100 |                                 |
|  1 | PRIMARY     | logs                        | eq_ref | PRIMARY,index_logs_on_cloud_log_id,index_logs_on_cloud_log_id_and_deleted_at,index_logs_on_cloud_log_id_and_deleted_at_and_timestamp                  | PRIMARY                                                 | 4       | PERF.id                                                               |    1 |                                 |
|  1 | PRIMARY     | cloud_logs                  | eq_ref | PRIMARY,index_cloud_logs_on_client_application_version_id                                                                                             | PRIMARY                                                 | 4       | cloudlog_production.logs.cloud_log_id                                 |    1 |                                 |
|  1 | PRIMARY     | client_application_versions | eq_ref | PRIMARY,index_client_application_versions_on_client_application_id                                                                                    | PRIMARY                                                 | 4       | cloudlog_production.cloud_logs.client_application_version_id          |    1 |                                 |
|  1 | PRIMARY     | client_applications         | eq_ref | PRIMARY                                                                                                                                               | PRIMARY                                                 | 4       | cloudlog_production.client_application_versions.client_application_id |    1 | Using index                     |
|  2 | DERIVED     | client_application_versions | const  | PRIMARY,index_client_application_versions_on_client_application_id                                                                                    | PRIMARY                                                 | 4       |                                                                       |    1 | Using temporary; Using filesort |
|  2 | DERIVED     | client_applications         | const  | PRIMARY,index_client_applications_on_account_id                                                                                                       | PRIMARY                                                 | 4       |                                                                       |    1 |                                 |
|  2 | DERIVED     | cloud_logs                  | ref    | PRIMARY,index_cloud_logs_on_client_application_version_id                                                                                             | index_cloud_logs_on_client_application_version_id       | 5       |                                                                       | 3578 | Using where; Using index        |
|  2 | DERIVED     | logs                        | ref    | index_logs_on_cloud_log_id,index_logs_on_deleted_at,index_logs_on_cloud_log_id_and_deleted_at,index_logs_on_cloud_log_id_and_deleted_at_and_timestamp | index_logs_on_cloud_log_id_and_deleted_at_and_timestamp | 14      | cloudlog_production.cloud_logs.id                                     |    4 | Using where; Using index        |
+----+-------------+-----------------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+---------+-----------------------------------------------------------------------+------+---------------------------------+
9 rows in set (0.03 sec)