1

I have a query, it is fairly big, but not THAT big. One the baseline environment, it runs in less than 1/3 of a second. When we moved the database to RDS in Amazon, it takes 10-15 minutes to run.

I enabled the slow query log, and it showed me that this query took 13:19:8344020 to run, and examined 615,608,975 (!) rows.

My explain statement suggested the following:

+----+-------------+----------------------------------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+---------+-------------------------------------+------+----------+----------------------------------------------+
| id | select_type | table                                  | partitions | type   | possible_keys                                                                                                                            | key                       | key_len | ref                                 | rows | filtered | Extra                                        |
+----+-------------+----------------------------------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+---------+-------------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | Nightmares                             | NULL       | index  | PRIMARY,fk_user_created_id4_idx,fk_user_modified_id4_idx,fk_resp_lev_id1_idx,fk_resp_team_id1_idx,sb_dt_ng,status,sub_dt,sub_type,idx_ng | PRIMARY                   | 8       | NULL                                | 1251 |    96.96 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | FilterInstitution                      | NULL       | ref    | fk_nightmare_id6_idx                                                                                                                     | fk_nightmare_id6_idx       | 8       | vwea.Nightmares.id                   |    1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | FilternightmaresNotes                  | NULL       | ref    | fk_note_nightmares_id1_idx                                                                                                               | fk_note_nightmares_id1_idx | 8       | vwea.Nightmares.id                   |    1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | FilterSupportingDocuments              | NULL       | ref    | fk_nightmare_id2_idx                                                                                                                     | fk_nightmare_id2_idx       | 9       | vwea.Nightmares.id                   |    1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | FilternightmaresUpdateCommunications   | NULL       | ref    | fk_uc_prop1_idx                                                                                                                          | fk_uc_prop1_idx           | 8       | vwea.Nightmares.id                   |    1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | FilternightmaresAdditionalCollaborators| NULL       | ref    | fk_pac_prop1_idx                                                                                                                         | fk_pac_prop1_idx          | 8       | vwea.Nightmares.id                   |    1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | FilterContact                          | NULL       | ref    | fk_nightmare_id3_idx                                                                                                                     | fk_nightmare_id3_idx       | 8       | vwea.Nightmares.id                   |    1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | FilterSiteLocations                    | NULL       | ref    | stlocfkpro_idx                                                                                                                           | stlocfkpro_idx            | 8       | vwea.Nightmares.id                   |    2 |   100.00 | Using index                                  |
|  1 | SIMPLE      | FilterEventInformation                 | NULL       | ref    | evtinfofkprop_idx                                                                                                                        | evtinfofkprop_idx         | 8       | vwea.Nightmares.id                   |    1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | FilterAdditionalDetails                | NULL       | ref    | adtl_fk_idx                                                                                                                              | adtl_fk_idx               | 8       | vwea.Nightmares.id                   |    1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | Tagged                                 | NULL       | ref    | fk_prop_mdl_id1_idx                                                                                                                      | fk_prop_mdl_id1_idx       | 9       | vwea.Nightmares.id                   |    2 |   100.00 | Using where                                  |
|  1 | SIMPLE      | Institutions                           | NULL       | ref    | fk_nightmare_id6_idx                                                                                                                     | fk_nightmare_id6_idx       | 8       | vwea.Nightmares.id                   |    1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | ApplicantTypes                         | NULL       | eq_ref | PRIMARY                                                                                                                                  | PRIMARY                   | 4       | vwea.Institutions.applicant_type_id |    1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | Categories                             | NULL       | eq_ref | PRIMARY                                                                                                                                  | PRIMARY                   | 4       | vwea.Institutions.category_id       |    1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | ResponsibleTeams                       | NULL       | eq_ref | PRIMARY                                                                                                                                  | PRIMARY                   | 8       | vwea.Nightmares.responsible_team_id  |    1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | ResponseLevels                         | NULL       | eq_ref | PRIMARY                                                                                                                                  | PRIMARY                   | 8       | vwea.Nightmares.response_level_id    |    1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | Contacts                               | NULL       | ref    | fk_nightmare_id3_idx                                                                                                                     | fk_nightmare_id3_idx       | 8       | vwea.Nightmares.id                   |    1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | SubmissionTypes                        | NULL       | eq_ref | PRIMARY                                                                                                                                  | PRIMARY                   | 4       | vwea.Nightmares.submission_type_id   |    1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | AdditionalDetails                      | NULL       | ref    | adtl_fk_idx                                                                                                                              | adtl_fk_idx               | 8       | vwea.Nightmares.id                   |    1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | EventInformation                       | NULL       | ref    | evtinfofkprop_idx                                                                                                                        | evtinfofkprop_idx         | 8       | vwea.Nightmares.id                   |    1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | Vendors                                | NULL       | ref    | vdfkprop_idx                                                                                                                             | vdfkprop_idx              | 8       | vwea.Nightmares.id                   |    1 |   100.00 | NULL                                         |
+----+-------------+----------------------------------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+---------+-------------------------------------+------+----------+----------------------------------------------+

The query returns a subset of the total records, which is around 1,300 in the table.

Query

The query is as follows:

SELECT 
  `Nightmares`.`id` AS `Nightmares__id`, 
  `Nightmares`.`instance_id` AS `Nightmares__instance_id`, 
  `Nightmares`.`submission_type_id` AS `Nightmares__submission_type_id`, 
  `Nightmares`.`is_next_generation` AS `Nightmares__is_next_generation`, 
  `Nightmares`.`title` AS `Nightmares__title`, 
  `Nightmares`.`executive_summary` AS `Nightmares__executive_summary`, 
  `Nightmares`.`comments` AS `Nightmares__comments`, 
  `Nightmares`.`created_by` AS `Nightmares__created_by`, 
  `Nightmares`.`modified_by` AS `Nightmares__modified_by`, 
  `Nightmares`.`submission_date` AS `Nightmares__submission_date`, 
  `Nightmares`.`attachments_count` AS `Nightmares__attachments_count`, 
  `Nightmares`.`supporting_documents_count` AS `Nightmares__supporting_documents_count`, 
  `Nightmares`.`notes_count` AS `Nightmares__notes_count`, 
  `Nightmares`.`tag_count` AS `Nightmares__tag_count`, 
  `Nightmares`.`credibility` AS `Nightmares__credibility`, 
  `Nightmares`.`uniqueness` AS `Nightmares__uniqueness`, 
  `Nightmares`.`evaluation_speed` AS `Nightmares__evaluation_speed`, 
  `Nightmares`.`evaluation_innovation` AS `Nightmares__evaluation_innovation`, 
  `Nightmares`.`evaluation_monetary_benefit` AS `Nightmares__evaluation_monetary_benefit`, 
  `Nightmares`.`evaluation_nonmonetary_benefit` AS `Nightmares__evaluation_nonmonetary_benefit`, 
  `Nightmares`.`evaluation_partnership_potential` AS `Nightmares__evaluation_partnership_potential`, 
  `Nightmares`.`status` AS `Nightmares__status`, 
  `Nightmares`.`is_deleted` AS `Nightmares__is_deleted`, 
  `Nightmares`.`forwarded_date` AS `Nightmares__forwarded_date`, 
  `Nightmares`.`responsible_team_id` AS `Nightmares__responsible_team_id`, 
  `Nightmares`.`status_change_date` AS `Nightmares__status_change_date`, 
  `Nightmares`.`response_level_id` AS `Nightmares__response_level_id`, 
  `Nightmares`.`working_team_id` AS `Nightmares__working_team_id`, 
  `Nightmares`.`next_step_id` AS `Nightmares__next_step_id`, 
  `Nightmares`.`created` AS `Nightmares__created`, 
  `Nightmares`.`modified` AS `Nightmares__modified`, 
  `Institutions`.`id` AS `Institutions__id`, 
  `Institutions`.`proposal_id` AS `Institutions__proposal_id`, 
  `Institutions`.`applicant_type_id` AS `Institutions__applicant_type_id`, 
  `Institutions`.`organization_name` AS `Institutions__organization_name`, 
  `Institutions`.`category_id` AS `Institutions__category_id`, 
  `Institutions`.`other` AS `Institutions__other`, 
  `Institutions`.`branch_id` AS `Institutions__branch_id`, 
  `Institutions`.`branch_other` AS `Institutions__branch_other`, 
  `Institutions`.`created_by` AS `Institutions__created_by`, 
  `Institutions`.`modified_by` AS `Institutions__modified_by`, 
  `Institutions`.`created` AS `Institutions__created`, 
  `Institutions`.`modified` AS `Institutions__modified`, 
  `ApplicantTypes`.`id` AS `ApplicantTypes__id`, 
  `ApplicantTypes`.`name` AS `ApplicantTypes__name`, 
  `ApplicantTypes`.`is_locked` AS `ApplicantTypes__is_locked`, 
  `ApplicantTypes`.`instance` AS `ApplicantTypes__instance`, 
  `ApplicantTypes`.`order` AS `ApplicantTypes__order`, 
  `ApplicantTypes`.`created_by` AS `ApplicantTypes__created_by`, 
  `ApplicantTypes`.`modified_by` AS `ApplicantTypes__modified_by`, 
  `ApplicantTypes`.`created` AS `ApplicantTypes__created`, 
  `ApplicantTypes`.`modified` AS `ApplicantTypes__modified`, 
  `Categories`.`id` AS `Categories__id`, 
  `Categories`.`applicant_type_id` AS `Categories__applicant_type_id`, 
  `Categories`.`is_locked` AS `Categories__is_locked`, 
  `Categories`.`name` AS `Categories__name`, 
  `Categories`.`default_credibility` AS `Categories__default_credibility`, 
  `Categories`.`created_by` AS `Categories__created_by`, 
  `Categories`.`modified_by` AS `Categories__modified_by`, 
  `Categories`.`order` AS `Categories__order`, 
  `Categories`.`created` AS `Categories__created`, 
  `Categories`.`modified` AS `Categories__modified`, 
  `ResponseLevels`.`id` AS `ResponseLevels__id`, 
  `ResponseLevels`.`name` AS `ResponseLevels__name`, 
  `ResponseLevels`.`description` AS `ResponseLevels__description`, 
  `ResponseLevels`.`is_locked` AS `ResponseLevels__is_locked`, 
  `ResponseLevels`.`created_by` AS `ResponseLevels__created_by`, 
  `ResponseLevels`.`modified_by` AS `ResponseLevels__modified_by`, 
  `ResponseLevels`.`created` AS `ResponseLevels__created`, 
  `ResponseLevels`.`modified` AS `ResponseLevels__modified`, 
  `ResponsibleTeams`.`id` AS `ResponsibleTeams__id`, 
  `ResponsibleTeams`.`name` AS `ResponsibleTeams__name`, 
  `ResponsibleTeams`.`description` AS `ResponsibleTeams__description`, 
  `ResponsibleTeams`.`emails_count` AS `ResponsibleTeams__emails_count`, 
  `ResponsibleTeams`.`is_locked` AS `ResponsibleTeams__is_locked`, 
  `ResponsibleTeams`.`Nightmares_count` AS `ResponsibleTeams__Nightmares_count`, 
  `ResponsibleTeams`.`created_by` AS `ResponsibleTeams__created_by`, 
  `ResponsibleTeams`.`modified_by` AS `ResponsibleTeams__modified_by`, 
  `ResponsibleTeams`.`created` AS `ResponsibleTeams__created`, 
  `ResponsibleTeams`.`modified` AS `ResponsibleTeams__modified`, 
  `Contacts`.`id` AS `Contacts__id`, 
  `Contacts`.`proposal_id` AS `Contacts__proposal_id`, 
  `Contacts`.`firstname` AS `Contacts__firstname`, 
  `Contacts`.`lastname` AS `Contacts__lastname`, 
  `Contacts`.`email_address` AS `Contacts__email_address`, 
  `Contacts`.`address1` AS `Contacts__address1`, 
  `Contacts`.`address2` AS `Contacts__address2`, 
  `Contacts`.`city` AS `Contacts__city`, 
  `Contacts`.`state` AS `Contacts__state`, 
  `Contacts`.`zip` AS `Contacts__zip`, 
  `Contacts`.`municipality_name` AS `Contacts__municipality_name`, 
  `Contacts`.`jurisdiction_organization` AS `Contacts__jurisdiction_organization`, 
  `Contacts`.`phone` AS `Contacts__phone`, 
  `Contacts`.`created_by` AS `Contacts__created_by`, 
  `Contacts`.`modified_by` AS `Contacts__modified_by`, 
  `Contacts`.`created` AS `Contacts__created`, 
  `Contacts`.`modified` AS `Contacts__modified`, 
  `SubmissionTypes`.`id` AS `SubmissionTypes__id`, 
  `SubmissionTypes`.`name` AS `SubmissionTypes__name`, 
  `SubmissionTypes`.`alias` AS `SubmissionTypes__alias`, 
  `SubmissionTypes`.`enabled` AS `SubmissionTypes__enabled`, 
  `SubmissionTypes`.`created_by` AS `SubmissionTypes__created_by`, 
  `SubmissionTypes`.`modified_by` AS `SubmissionTypes__modified_by`, 
  `SubmissionTypes`.`created` AS `SubmissionTypes__created`, 
  `SubmissionTypes`.`modified` AS `SubmissionTypes__modified`, 
  `AdditionalDetails`.`id` AS `AdditionalDetails__id`, 
  `AdditionalDetails`.`proposal_id` AS `AdditionalDetails__proposal_id`, 
  `AdditionalDetails`.`general_suggestions` AS `AdditionalDetails__general_suggestions`, 
  `AdditionalDetails`.`plans_policies_in_place` AS `AdditionalDetails__plans_policies_in_place`, 
  `AdditionalDetails`.`relevant_details` AS `AdditionalDetails__relevant_details`, 
  `AdditionalDetails`.`created` AS `AdditionalDetails__created`, 
  `AdditionalDetails`.`modified` AS `AdditionalDetails__modified`, 
  `EventInformation`.`id` AS `EventInformation__id`, 
  `EventInformation`.`proposal_id` AS `EventInformation__proposal_id`, 
  `EventInformation`.`entry_type` AS `EventInformation__entry_type`, 
  `EventInformation`.`name` AS `EventInformation__name`, 
  `EventInformation`.`location` AS `EventInformation__location`, 
  `EventInformation`.`participants_partners` AS `EventInformation__participants_partners`, 
  `EventInformation`.`ea_role` AS `EventInformation__ea_role`, 
  `EventInformation`.`number_attendees` AS `EventInformation__number_attendees`, 
  `EventInformation`.`is_open_press` AS `EventInformation__is_open_press`, 
  `EventInformation`.`estimated_attendance_cost` AS `EventInformation__estimated_attendance_cost`, 
  `EventInformation`.`general_suggestions` AS `EventInformation__general_suggestions`, 
  `EventInformation`.`created` AS `EventInformation__created`, 
  `EventInformation`.`modified` AS `EventInformation__modified`, 
  `EventInformation`.`created_by` AS `EventInformation__created_by`, 
  `EventInformation`.`modified_by` AS `EventInformation__modified_by`, 
  `Vendors`.`id` AS `Vendors__id`, 
  `Vendors`.`proposal_id` AS `Vendors__proposal_id`, 
  `Vendors`.`legal_entity_name` AS `Vendors__legal_entity_name`, 
  `Vendors`.`hq_address` AS `Vendors__hq_address`, 
  `Vendors`.`hq_city` AS `Vendors__hq_city`, 
  `Vendors`.`hq_state` AS `Vendors__hq_state`, 
  `Vendors`.`hq_zip` AS `Vendors__hq_zip`, 
  `Vendors`.`hq_duns` AS `Vendors__hq_duns`, 
  `Vendors`.`primary_services` AS `Vendors__primary_services`, 
  `Vendors`.`description` AS `Vendors__description`, 
  `Vendors`.`created` AS `Vendors__created`, 
  `Vendors`.`modified` AS `Vendors__modified`, 
  `Vendors`.`created_by` AS `Vendors__created_by`, 
  `Vendors`.`modified_by` AS `Vendors__modified_by` 
FROM 
  `nightmares` `Nightmares` 
  left JOIN `institutions` `FilterInstitution` ON FilterInstitution.proposal_id = Nightmares.id 
  left JOIN `Nightmares_notes` `FilterNightmaresNotes` ON FilterNightmaresNotes.proposal_id = Nightmares.id 
  left JOIN `supporting_documents` `FilterSupportingDocuments` ON FilterSupportingDocuments.proposal_id = Nightmares.id 
  left JOIN `Nightmares_update_communications` `FilterNightmaresUpdateCommunications` ON FilterNightmaresUpdateCommunications.proposal_id = Nightmares.id 
  left JOIN `Nightmares_additional_collaborators` `FilterNightmaresAdditionalCollaborators` ON FilterNightmaresAdditionalCollaborators.proposal_id = Nightmares.id 
  left JOIN `contacts` `FilterContact` ON FilterContact.proposal_id = Nightmares.id 
  left JOIN `site_locations` `FilterSiteLocations` ON FilterSiteLocations.proposal_id = Nightmares.id 
  left JOIN `event_information` `FilterEventInformation` ON FilterEventInformation.proposal_id = Nightmares.id 
  left JOIN `additional_details` `FilterAdditionalDetails` ON FilterAdditionalDetails.proposal_id = Nightmares.id 
  left JOIN `tags_tagged` `Tagged` ON (
    Tagged.fk_id = Nightmares.id 
    AND `Tagged`.`fk_model` = 'Nightmares'
  ) 
  LEFT JOIN `institutions` `Institutions` ON `Nightmares`.`id` = (`Institutions`.`proposal_id`) 
  LEFT JOIN `applicant_types` `ApplicantTypes` ON `ApplicantTypes`.`id` = (
    `Institutions`.`applicant_type_id`
  ) 
  LEFT JOIN `categories` `Categories` ON `Categories`.`id` = (`Institutions`.`category_id`) 
  LEFT JOIN `responsible_teams` `ResponsibleTeams` ON `ResponsibleTeams`.`id` = (
    `Nightmares`.`responsible_team_id`
  ) 
  LEFT JOIN `response_levels` `ResponseLevels` ON `ResponseLevels`.`id` = (
    `Nightmares`.`response_level_id`
  ) 
  LEFT JOIN `contacts` `Contacts` ON `Nightmares`.`id` = (`Contacts`.`proposal_id`) 
  LEFT JOIN `submission_types` `SubmissionTypes` ON `SubmissionTypes`.`id` = (
    `Nightmares`.`submission_type_id`
  ) 
  LEFT JOIN `additional_details` `AdditionalDetails` ON `Nightmares`.`id` = (
    `AdditionalDetails`.`proposal_id`
  ) 
  LEFT JOIN `event_information` `EventInformation` ON `Nightmares`.`id` = (
    `EventInformation`.`proposal_id`
  ) 
  LEFT JOIN `vendors` `Vendors` ON `Nightmares`.`id` = (`Vendors`.`proposal_id`) 
WHERE 
  `Nightmares`.`is_next_generation` = 1 
GROUP BY 
  `Nightmares`.`id` 
ORDER BY 
  `Nightmares`.`submission_date` DESC 
LIMIT 
  12 OFFSET 0

Why is there such a massive variance between the two servers? Why are there so many rows being scanned? Why does one perform in fractions of a second, while the other is still packing its bags two days later?

  • Did you update statistics on a secondary server after migration? – Natalia Jul 10 at 22:56
  • @Natalia what do you mean? – Barry Chapman Jul 11 at 1:49
  • Could you post the EXPLAIN from your baseline that runs in 1/3 of a second AND EXPLAIN from RDS environment? Then we will have a starting point to compare to RDS explain. On your secondary server, SHOW INDEX FROM tbl_name; for each of the 21 tables involved will get your statistics updated. – Wilson Hauck Jul 13 at 21:17
  • @BarryChapman Have you made any progress with the long run time on RDS? – Wilson Hauck Jul 18 at 22:00
  • 1
    @BarryChapman Thanks for the good news. There are so many details that can cause our efforts to 'go off the rails'. Glad you are doing well now. For free Utility Scripts see my profile, Network Profile for contact info. – Wilson Hauck Jul 20 at 1:16
0

Get rid of the GROUP BY. And add the composite index (is_next_generation, submission_date).

  • Then I end up with 12 records that are identical – Barry Chapman Jul 11 at 14:58
  • Tell us which of those LEFT JOINs are 1:many. The EXPLAIN is saying that most are 1:1 (or 1:0), that that apparently is not the case? – Rick James Jul 11 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.