gistfile1.sql

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
  INNER JOIN client_application_versions as cav1 use index for join (`index_cavs_on_client_application_id_and_public_key`)
    ON
      cav1.id = cl1.client_application_version_id
--  INNER JOIN client_application_versions as cav2
--    ON
--      cav2.id = cl2.client_application_version_id
  INNER JOIN client_applications as ca
    ON
      ca.id = cav1.client_application_id
WHERE
  (l.deleted_at IS NULL)
    AND
  (ca.account_id = 1)
    AND
  (ca.public_key = 'p0kZudG0')
    AND
  (cav1.public_key = '0HgoJRyE')
ORDER BY
  l.timestamp DESC
LIMIT
  100, 100
+----+-------------+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+---------+----------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys                                                                                                                                         | key                                                | key_len | ref                              | rows | Extra                                        |
+----+-------------+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+---------+----------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | ca    | ref  | PRIMARY,index_client_applications_on_account_id,index_client_applications_on_public_key                                                               | index_client_applications_on_public_key            | 768     | const                            |    1 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | cav1  | ref  | index_cavs_on_client_application_id_and_public_key                                                                                                    | index_cavs_on_client_application_id_and_public_key | 773     | cloudlog_production.ca.id,const  |    1 | Using where; Using index                     |
|  1 | SIMPLE      | cl1   | ref  | PRIMARY,index_cloud_logs_on_client_application_version_id                                                                                             | index_cloud_logs_on_client_application_version_id  | 5       | cloudlog_production.cav1.id      |  417 | Using where; Using index                     |
|  1 | SIMPLE      | l     | ref  | index_logs_on_cloud_log_id_and_deleted_at_and_timestamp,index_logs_on_cloud_log_id_and_deleted_at,index_logs_on_cloud_log_id,index_logs_on_deleted_at | index_logs_on_cloud_log_id_and_deleted_at          | 14      | cloudlog_production.cl1.id,const |    4 | Using where                                  |
+----+-------------+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+---------+----------------------------------+------+----------------------------------------------+