describe SELECT `issues`.`id` AS t0_r0, `issues`.`tracker_id` AS t0_r1, `issues`.`project_id` AS t0_r2, `issues`.`subject` AS t0_r3, `issues`.`description` AS t0_r4, `issues`.`due_date` AS t0_r5, `issues`.`category_id` AS t0_r6, `issues`.`status_id` AS t0_r7, `issues`.`assigned_to_id` AS t0_r8, `issues`.`priority_id` AS t0_r9, `issues`.`fixed_version_id` AS t0_r10, `issues`.`author_id` AS t0_r11, `issues`.`lock_version` AS t0_r12, `issues`.`created_on` AS t0_r13, `issues`.`updated_on` AS t0_r14, `issues`.`start_date` AS t0_r15, `issues`.`done_ratio` AS t0_r16, `issues`.`estimated_hours` AS t0_r17, `issues`.`parent_id` AS t0_r18, `issues`.`root_id` AS t0_r19, `issues`.`lft` AS t0_r20, `issues`.`rgt` AS t0_r21, `issues`.`is_private` AS t0_r22, `issues`.`closed_on` AS t0_r23, `projects`.`id` AS t1_r0, `projects`.`name` AS t1_r1, `projects`.`description` AS t1_r2, `projects`.`homepage` AS t1_r3, `projects`.`is_public` AS t1_r4, `projects`.`parent_id` AS t1_r5, `projects`.`created_on` AS t1_r6, `projects`.`updated_on` AS t1_r7, `projects`.`identifier` AS t1_r8, `projects`.`status` AS t1_r9, `projects`.`lft` AS t1_r10, `projects`.`rgt` AS t1_r11, `projects`.`inherit_members` AS t1_r12, `projects`.`hipchat_auth_token` AS t1_r13, `projects`.`hipchat_room_name` AS t1_r14, `projects`.`hipchat_notify` AS t1_r15, `issue_statuses`.`id` AS t2_r0, `issue_statuses`.`name` AS t2_r1, `issue_statuses`.`is_closed` AS t2_r2, `issue_statuses`.`is_default` AS t2_r3, `issue_statuses`.`position` AS t2_r4, `issue_statuses`.`default_done_ratio` AS t2_r5, `users`.`id` AS t3_r0, `users`.`login` AS t3_r1, `users`.`hashed_password` AS t3_r2, `users`.`firstname` AS t3_r3, `users`.`lastname` AS t3_r4, `users`.`mail` AS t3_r5, `users`.`admin` AS t3_r6, `users`.`status` AS t3_r7, `users`.`last_login_on` AS t3_r8, `users`.`language` AS t3_r9, `users`.`auth_source_id` AS t3_r10, `users`.`created_on` AS t3_r11, `users`.`updated_on` AS t3_r12, `users`.`type` AS t3_r13, `users`.`identity_url` AS t3_r14, `users`.`mail_notification` AS t3_r15, `users`.`salt` AS t3_r16, `users`.`must_change_passwd` AS t3_r17, `users`.`phone` AS t3_r18, `users`.`address` AS t3_r19, `users`.`skype` AS t3_r20, `users`.`birthday` AS t3_r21, `users`.`job_title` AS t3_r22, `users`.`company` AS t3_r23, `users`.`middlename` AS t3_r24, `users`.`gender` AS t3_r25, `users`.`twitter` AS t3_r26, `users`.`facebook` AS t3_r27, `users`.`linkedin` AS t3_r28, `users`.`background` AS t3_r29, `users`.`appearance_date` AS t3_r30, `users`.`department_id` AS t3_r31, `users`.`reminder_notification` AS t3_r32, `trackers`.`id` AS t4_r0, `trackers`.`name` AS t4_r1, `trackers`.`is_in_chlog` AS t4_r2, `trackers`.`position` AS t4_r3, `trackers`.`is_in_roadmap` AS t4_r4, `trackers`.`fields_bits` AS t4_r5, `enumerations`.`id` AS t5_r0, `enumerations`.`name` AS t5_r1, `enumerations`.`position` AS t5_r2, `enumerations`.`is_default` AS t5_r3, `enumerations`.`type` AS t5_r4, `enumerations`.`active` AS t5_r5, `enumerations`.`project_id` AS t5_r6, `enumerations`.`parent_id` AS t5_r7, `enumerations`.`position_name` AS t5_r8, `issue_categories`.`id` AS t6_r0, `issue_categories`.`project_id` AS t6_r1, `issue_categories`.`name` AS t6_r2, `issue_categories`.`assigned_to_id` AS t6_r3, `versions`.`id` AS t7_r0, `versions`.`project_id` AS t7_r1, `versions`.`name` AS t7_r2, `versions`.`description` AS t7_r3, `versions`.`effective_date` AS t7_r4, `versions`.`created_on` AS t7_r5, `versions`.`updated_on` AS t7_r6, `versions`.`wiki_page_title` AS t7_r7, `versions`.`status` AS t7_r8, `versions`.`sharing` AS t7_r9, `issue_reads`.`id` AS t8_r0, `issue_reads`.`user_id` AS t8_r1, `issue_reads`.`issue_id` AS t8_r2, `issue_reads`.`read_date` AS t8_r3, `issue_reads`.`created_at` AS t8_r4, `issue_reads`.`updated_at` AS t8_r5 FROM `issues` INNER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` INNER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` LEFT OUTER JOIN `users` ON `users`.`id` = `issues`.`assigned_to_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `enumerations` ON `enumerations`.`id` = `issues`.`priority_id` AND `enumerations`.`type` IN ('IssuePriority') LEFT OUTER JOIN `issue_categories` ON `issue_categories`.`id` = `issues`.`category_id` LEFT OUTER JOIN `versions` ON `versions`.`id` = `issues`.`fixed_version_id` LEFT OUTER JOIN `issue_reads` ON `issue_reads`.`issue_id` = `issues`.`id` AND issue_reads.user_id = issues.assigned_to_id LEFT JOIN issue_reads uis_ir ON uis_ir.issue_id = issues.id and uis_ir.user_id = issues.assigned_to_id WHERE (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=0))) ORDER BY issues.id DESC LIMIT 50 OFFSET 0; ## BEFORE INDEXES +----+--------------------+------------------+-----------------+---------------------------------------------+----------------------------+---------+-----------------------------------+-------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------------+-----------------+---------------------------------------------+----------------------------+---------+-----------------------------------+-------+----------------------------------------------+ | 1 | PRIMARY | issues | ALL | issues_project_id,index_issues_on_status_id | NULL | NULL | NULL | 56755 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | issue_statuses | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.status_id | 1 | | | 1 | PRIMARY | users | eq_ref | PRIMARY,index_users_on_id_and_type | PRIMARY | 4 | redmineit.issues.assigned_to_id | 1 | | | 1 | PRIMARY | trackers | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.tracker_id | 1 | | | 1 | PRIMARY | enumerations | eq_ref | PRIMARY,index_enumerations_on_id_and_type | PRIMARY | 4 | redmineit.issues.priority_id | 1 | | | 1 | PRIMARY | projects | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.project_id | 1 | Using where | | 1 | PRIMARY | issue_categories | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.category_id | 1 | | | 1 | PRIMARY | versions | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.fixed_version_id | 1 | | | 1 | PRIMARY | issue_reads | ALL | NULL | NULL | NULL | NULL | 57280 | | | 1 | PRIMARY | uis_ir | ALL | NULL | NULL | NULL | NULL | 57280 | | | 3 | DEPENDENT SUBQUERY | issue_statuses | unique_subquery | PRIMARY,index_issue_statuses_on_is_closed | PRIMARY | 4 | func | 1 | Using where | | 2 | DEPENDENT SUBQUERY | em | index_subquery | enabled_modules_project_id | enabled_modules_project_id | 5 | func | 3 | Using where | +----+--------------------+------------------+-----------------+---------------------------------------------+----------------------------+---------+-----------------------------------+-------+----------------------------------------------+ 12 rows in set (0.01 sec) ## AFTER INDEXES +----+--------------------+------------------+-----------------+---------------------------------------------+----------------------------+---------+-----------------------------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------------+-----------------+---------------------------------------------+----------------------------+---------+-----------------------------------------------------+------+----------------------------------------------+ | 1 | PRIMARY | projects | ALL | PRIMARY | NULL | NULL | NULL | 33 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | issues | ref | issues_project_id,index_issues_on_status_id | issues_project_id | 4 | redmineit.projects.id | 596 | Using where | | 1 | PRIMARY | users | eq_ref | PRIMARY,index_users_on_id_and_type | PRIMARY | 4 | redmineit.issues.assigned_to_id | 1 | | | 1 | PRIMARY | trackers | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.tracker_id | 1 | | | 1 | PRIMARY | enumerations | eq_ref | PRIMARY,index_enumerations_on_id_and_type | PRIMARY | 4 | redmineit.issues.priority_id | 1 | | | 1 | PRIMARY | issue_statuses | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.status_id | 1 | | | 1 | PRIMARY | issue_categories | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.category_id | 1 | | | 1 | PRIMARY | versions | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.fixed_version_id | 1 | | | 1 | PRIMARY | issue_reads | ref | ir_issues_users | ir_issues_users | 10 | redmineit.issues.id,redmineit.issues.assigned_to_id | 1 | | | 1 | PRIMARY | uis_ir | ref | ir_issues_users | ir_issues_users | 10 | redmineit.issues.id,redmineit.issues.assigned_to_id | 1 | Using index | | 3 | DEPENDENT SUBQUERY | issue_statuses | unique_subquery | PRIMARY,index_issue_statuses_on_is_closed | PRIMARY | 4 | func | 1 | Using where | | 2 | DEPENDENT SUBQUERY | em | index_subquery | enabled_modules_project_id,em_name | enabled_modules_project_id | 5 | func | 3 | Using where | +----+--------------------+------------------+-----------------+---------------------------------------------+----------------------------+---------+-----------------------------------------------------+------+----------------------------------------------+ 12 rows in set (0.00 sec)