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
    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
  0, 100
+----+-------------+-------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+------------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                                                                                                                         | key                                               | key_len | ref                                            | rows | Extra                                        |
+----+-------------+-------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+------------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | cav1  | ref    | PRIMARY,index_client_application_versions_on_client_application_id,index_client_application_versions_on_public_key                                    | index_client_application_versions_on_public_key   | 768     | const                                          |    1 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | ca    | eq_ref | PRIMARY,index_client_applications_on_account_id,index_client_applications_on_public_key                                                               | PRIMARY                                           | 4       | cloudlog_production.cav1.client_application_id |    1 | Using where                                  |
|  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                                  |
+----+-------------+-------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+------------------------------------------------+------+----------------------------------------------+
4 rows in set (0.52 sec)