public
Created

  • Download Gist
gistfile1.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
 
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)

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.