Skip to content

Instantly share code, notes, and snippets.

@coneybeare

coneybeare/gist:4531485

Created January 14, 2013 16:55
Show Gist options
  • Save coneybeare/4531485 to your computer and use it in GitHub Desktop.
Save coneybeare/4531485 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.sql
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
+----+-------------+----------------------------------+--------+-----------------------------------------------------------------------------------------------+---------------------------------------------------+---------+------------------------------------------------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------------------+--------+-----------------------------------------------------------------------------------------------+---------------------------------------------------+---------+------------------------------------------------------------------------+------+---------------------------------+
| 1 | SIMPLE | client_application_versions | const | PRIMARY,index_client_application_versions_on_client_application_id | PRIMARY | 4 | const | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | client_applications | const | PRIMARY,index_client_applications_on_account_id | PRIMARY | 4 | const | 1 | |
| 1 | SIMPLE | cloud_logs | ref | PRIMARY,index_cloud_logs_on_client_application_version_id | index_cloud_logs_on_client_application_version_id | 5 | const | 3346 | Using where; Using index |
| 1 | SIMPLE | cloud_logs_logs_join | eq_ref | PRIMARY,index_cloud_logs_on_client_application_version_id | PRIMARY | 4 | cloudlog_production.cloud_logs.id | 1 | |
| 1 | SIMPLE | client_application_versions_logs | eq_ref | PRIMARY | PRIMARY | 4 | cloudlog_production.cloud_logs_logs_join.client_application_version_id | 1 | Using index |
| 1 | SIMPLE | 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 | 14 | cloudlog_production.cloud_logs.id,const | 4 | Using where |
+----+-------------+----------------------------------+--------+-----------------------------------------------------------------------------------------------+---------------------------------------------------+---------+------------------------------------------------------------------------+------+---------------------------------+
6 rows in set (0.73 sec)
Using index_logs_on_cloud_log_id_and_deleted_at_and_timestamp index: 65.290186073 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 300
+----+-------------+----------------------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------------------------------------------------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------------------------------------------------------------------------+------+---------------------------------+
| 1 | SIMPLE | client_application_versions | const | PRIMARY,index_client_application_versions_on_client_application_id | PRIMARY | 4 | const | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | client_applications | const | PRIMARY,index_client_applications_on_account_id | PRIMARY | 4 | const | 1 | |
| 1 | SIMPLE | cloud_logs | ref | PRIMARY,index_cloud_logs_on_client_application_version_id | index_cloud_logs_on_client_application_version_id | 5 | const | 3346 | Using where; Using index |
| 1 | SIMPLE | cloud_logs_logs_join | eq_ref | PRIMARY,index_cloud_logs_on_client_application_version_id | PRIMARY | 4 | cloudlog_production.cloud_logs.id | 1 | |
| 1 | SIMPLE | client_application_versions_logs | eq_ref | PRIMARY | PRIMARY | 4 | cloudlog_production.cloud_logs_logs_join.client_application_version_id | 1 | Using index |
| 1 | SIMPLE | 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,const | 4 | Using where |
+----+-------------+----------------------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------------------------------------------------------------------------+------+---------------------------------+
6 rows in set (0.19 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment