2

This is a follow on question to optimise my.ini for key query

Data model:

DROP TABLE IF EXISTS `contract`;
CREATE TABLE IF NOT EXISTS `contract` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `organisation_id` int(11) NOT NULL,
  `code` varchar(3) NOT NULL,
  `moh_code` varchar(20) NOT NULL,
  `moh_number` varchar(20) NOT NULL,
  `moh_variation` varchar(20) NOT NULL,
  `description` mediumtext NOT NULL,
  `start` datetime NOT NULL,
  `finish` datetime NOT NULL,
  `cities` text,
  `is_support_contract` tinyint(1) DEFAULT NULL,
  `is_intensive` tinyint(1) DEFAULT NULL,
  `moh_team_type` varchar(4) DEFAULT NULL,
  `moh_team_setting` varchar(1) DEFAULT NULL,
  `moh_service_type` varchar(2) DEFAULT NULL,
  `moh_target_population` int(4) DEFAULT NULL,
  `moh_facility_id` varchar(10) DEFAULT NULL,
  `moh_open_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `organisation_id_idx` (`organisation_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

-- --------------------------------------------------------

--
-- Table structure for table `peer_engagement`
--

DROP TABLE IF EXISTS `peer_engagement`;
CREATE TABLE IF NOT EXISTS `peer_engagement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `peer_id` int(11) NOT NULL,
  `ps_number_id` int(11) NOT NULL,
  `service_organisation_id` int(11) DEFAULT NULL,
  `past_service_disengaged` datetime DEFAULT NULL,
  `key_worker_id` int(11) DEFAULT NULL,
  `iss_clinical_service_id` int(11) DEFAULT NULL,
  `iss_psychiatrist_id` int(11) DEFAULT NULL,
  `iss_service_coordinator_id` int(11) DEFAULT NULL,
  `iss_declined_courier_back_address` varchar(255) DEFAULT NULL,
  `waitinglist_support_suggestions_made` mediumtext,
  `waiting_psw_id` int(11) DEFAULT NULL,
  `declined_waiting_what_support_avail` mediumtext,
  `referral_source_id` int(11) DEFAULT NULL,
  `iss_referred_by` int(11) DEFAULT NULL,
  `record_entered_by` int(11) NOT NULL,
  `record_entered` datetime NOT NULL,
  `waiting_list_priority_id` int(11) DEFAULT NULL,
  `assigned_psw_id` int(11) DEFAULT NULL,
  `assigned` datetime DEFAULT NULL,
  `assigned_by` int(11) DEFAULT NULL,
  `assigned_effective` datetime DEFAULT NULL,
  `last_reengaged` datetime DEFAULT NULL,
  `last_reengaged_id` int(11) DEFAULT NULL,
  `disengagement_started` datetime DEFAULT NULL,
  `disengagement_target` datetime DEFAULT NULL,
  `disengagement_type_id` int(11) DEFAULT NULL,
  `disengagement_comments` mediumtext,
  `status_id` int(11) DEFAULT NULL,
  `closed` datetime DEFAULT NULL,
  `closed_by` int(11) DEFAULT NULL,
  `is_intensive` varchar(5) DEFAULT NULL,
  `heard_about_us_id` int(11) DEFAULT NULL,
  `primhd_referral_no` varchar(50) DEFAULT NULL,
  `moved_to_and_delete_id` int(4) DEFAULT NULL,
  `deleted_date` datetime DEFAULT NULL,
  `is_gp_only` tinyint(4) NOT NULL,
  `referral_to_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `peer_id_idx` (`peer_id`),
  KEY `ps_number_id_idx` (`ps_number_id`),
  KEY `service_organisation_id_idx` (`service_organisation_id`),
  KEY `key_worker_id_idx` (`key_worker_id`),
  KEY `iss_clinical_service_id_idx` (`iss_clinical_service_id`),
  KEY `iss_psychiatrist_id_idx` (`iss_psychiatrist_id`),
  KEY `iss_service_coordinator_id_idx` (`iss_service_coordinator_id`),
  KEY `referral_source_id_idx` (`referral_source_id`),
  KEY `iss_referred_by_idx` (`iss_referred_by`),
  KEY `record_entered_by_idx` (`record_entered_by`),
  KEY `waiting_psw_id_idx` (`waiting_psw_id`),
  KEY `waiting_list_priority_id_idx` (`waiting_list_priority_id`),
  KEY `assigned_psw_id_idx` (`assigned_psw_id`),
  KEY `assigned_by_idx` (`assigned_by`),
  KEY `last_reengaged_id_idx` (`last_reengaged_id`),
  KEY `disengagement_type_id_idx` (`disengagement_type_id`),
  KEY `status_id_idx` (`status_id`),
  KEY `closed_by_idx` (`closed_by`),
  KEY `peer_engagement_heard_about_us_id_fk` (`heard_about_us_id`),
  KEY `moved_to_and_delete_id_foreign_key` (`moved_to_and_delete_id`),
  KEY `deleted_date` (`deleted_date`),
  KEY `disengagement_target` (`disengagement_target`),
  KEY `peer_engagement_referral_to_id_fk` (`referral_to_id`),
  KEY `is_intensive` (`is_intensive`),
  KEY `is_intensive_2` (`is_intensive`,`status_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1502 ;

-- --------------------------------------------------------

--
-- Table structure for table `peer_number`
--

DROP TABLE IF EXISTS `peer_number`;
CREATE TABLE IF NOT EXISTS `peer_number` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ps_number` varchar(5) NOT NULL,
  `contract_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `contract_id_idx` (`contract_id`),
  KEY `ps_number` (`ps_number`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=75981 ;

-- --------------------------------------------------------

--
-- Table structure for table `person`
--

DROP TABLE IF EXISTS `person`;
CREATE TABLE IF NOT EXISTS `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `suite` varchar(100) DEFAULT NULL,
  `hnr_street` varchar(200) NOT NULL,
  `suburb` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `postcode` varchar(5) NOT NULL,
  `manual_address_entry` tinyint(1) DEFAULT NULL,
  `current_living_situation_id` int(11) DEFAULT NULL,
  `dhb_area_id` int(11) DEFAULT NULL,
  `email` varchar(120) DEFAULT NULL,
  `phone_daytime` varchar(25) DEFAULT NULL,
  `phone_evening` varchar(25) DEFAULT NULL,
  `mobile` varchar(25) DEFAULT NULL,
  `fax` varchar(25) DEFAULT NULL,
  `emergency_contact_details` text,
  `notes` mediumtext,
  `dob` date DEFAULT NULL,
  `nhi` varchar(7) DEFAULT NULL,
  `gender_id` int(11) DEFAULT NULL,
  `ethnicity_id` int(11) DEFAULT NULL,
  `team_leader_id` int(11) DEFAULT NULL,
  `organisation_id` int(11) DEFAULT NULL,
  `role` varchar(100) DEFAULT NULL,
  `type` varchar(255) NOT NULL,
  `postal_address` varchar(200) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `deleted_date` datetime DEFAULT NULL,
  `moved_to_and_delete_id` int(4) DEFAULT NULL,
  `employment_situation_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `user_id_idx` (`user_id`),
  KEY `current_living_situation_id_idx` (`current_living_situation_id`),
  KEY `dhb_area_id_idx` (`dhb_area_id`),
  KEY `gender_id_idx` (`gender_id`),
  KEY `team_leader_id_idx` (`team_leader_id`),
  KEY `organisation_id_idx` (`organisation_id`),
  KEY `moved_to_and_delete_id_foreign_key_person` (`moved_to_and_delete_id`),
  KEY `person_employment_situation_id_fk` (`employment_situation_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2377 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `contract`
--
ALTER TABLE `contract`
  ADD CONSTRAINT `contract_organisation_id_organisation_id` FOREIGN KEY (`organisation_id`) REFERENCES `organisation` (`id`);

--
-- Constraints for table `peer_engagement`
--
ALTER TABLE `peer_engagement`
  ADD CONSTRAINT `moved_to_and_delete_id_foreign_key` FOREIGN KEY (`moved_to_and_delete_id`) REFERENCES `peer_engagement` (`id`),
  ADD CONSTRAINT `peer_engagement_assigned_by_person_id` FOREIGN KEY (`assigned_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_assigned_psw_id_person_id` FOREIGN KEY (`assigned_psw_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_closed_by_person_id` FOREIGN KEY (`closed_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_disengagement_type_id_disengagement_type_id` FOREIGN KEY (`disengagement_type_id`) REFERENCES `disengagement_type` (`id`),
  ADD CONSTRAINT `peer_engagement_heard_about_us_id_fk` FOREIGN KEY (`heard_about_us_id`) REFERENCES `heard_about_us` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_clinical_service_id_organisation_id` FOREIGN KEY (`iss_clinical_service_id`) REFERENCES `organisation` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_psychiatrist_id_person_id` FOREIGN KEY (`iss_psychiatrist_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_referred_by_person_id` FOREIGN KEY (`iss_referred_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_service_coordinator_id_person_id` FOREIGN KEY (`iss_service_coordinator_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_key_worker_id_person_id` FOREIGN KEY (`key_worker_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_last_reengaged_id_peer_number_id` FOREIGN KEY (`last_reengaged_id`) REFERENCES `peer_number` (`id`),
  ADD CONSTRAINT `peer_engagement_peer_id_person_id` FOREIGN KEY (`peer_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_ps_number_id_peer_number_id` FOREIGN KEY (`ps_number_id`) REFERENCES `peer_number` (`id`),
  ADD CONSTRAINT `peer_engagement_record_entered_by_person_id` FOREIGN KEY (`record_entered_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_referral_source_id_referral_source_id` FOREIGN KEY (`referral_source_id`) REFERENCES `referral_source` (`id`),
  ADD CONSTRAINT `peer_engagement_referral_to_id_fk` FOREIGN KEY (`referral_to_id`) REFERENCES `referral_to` (`id`),
  ADD CONSTRAINT `peer_engagement_service_organisation_id_organisation_id` FOREIGN KEY (`service_organisation_id`) REFERENCES `organisation` (`id`),
  ADD CONSTRAINT `peer_engagement_status_id_status_id` FOREIGN KEY (`status_id`) REFERENCES `status` (`id`),
  ADD CONSTRAINT `peer_engagement_waiting_psw_id_person_id` FOREIGN KEY (`waiting_psw_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `pwwi` FOREIGN KEY (`waiting_list_priority_id`) REFERENCES `waiting_list_priority` (`id`);

--
-- Constraints for table `peer_number`
--
ALTER TABLE `peer_number`
  ADD CONSTRAINT `peer_number_contract_id_contract_id` FOREIGN KEY (`contract_id`) REFERENCES `contract` (`id`);

--
-- Constraints for table `person`
--
ALTER TABLE `person`
  ADD CONSTRAINT `moved_to_and_delete_id_foreign_key_person` FOREIGN KEY (`moved_to_and_delete_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `person_current_living_situation_id_current_living_situation_id` FOREIGN KEY (`current_living_situation_id`) REFERENCES `current_living_situation` (`id`),
  ADD CONSTRAINT `person_dhb_area_id_contract_id` FOREIGN KEY (`dhb_area_id`) REFERENCES `contract` (`id`),
  ADD CONSTRAINT `person_employment_situation_id_fk` FOREIGN KEY (`employment_situation_id`) REFERENCES `employment_situation` (`id`),
  ADD CONSTRAINT `person_gender_id_gender_id` FOREIGN KEY (`gender_id`) REFERENCES `gender` (`id`),
  ADD CONSTRAINT `person_organisation_id_organisation_id` FOREIGN KEY (`organisation_id`) REFERENCES `organisation` (`id`),
  ADD CONSTRAINT `person_team_leader_id_person_id` FOREIGN KEY (`team_leader_id`) REFERENCES `person` (`id`);

Queriy 1:

1) [EXPLAIN] 
  SELECT p.id AS p__id, p.status_id AS p__status_id,     p.disengagement_target AS p__disengagement_target, p2.id AS p2__id, p2.name AS p2__name, p3.id AS p3__id, p3.name AS p3__name, p4.id AS p4__id, p4.ps_number AS p4__ps_number, c.id AS c__id, c.code AS c__code, p5.id AS p5__id, p5.nhi AS p5__nhi, COALESCE(p2.name, p3.name) AS p2__0 
  FROM peer_engagement p 
    LEFT JOIN person p2 ON p.assigned_psw_id = p2.id 
    LEFT JOIN person p3 ON p.waiting_psw_id = p3.id 
    INNER JOIN peer_number p4 ON p.ps_number_id = p4.id 
    INNER JOIN contract c ON p4.contract_id = c.id 
    INNER JOIN person p5 ON p.peer_id = p5.id 
  WHERE (p.is_intensive = 0 
     AND p.status_id in (0, 1, 2, 3, 4, 5, 7)) 
  LIMIT 20

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  p   range   peer_id_idx,ps_number_id_idx,status_id_idx,is_inte...   is_intensive_2  10  NULL    611 Using where
1   SIMPLE  p2  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.assigned_psw_id   1   
1   SIMPLE  p3  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.waiting_psw_id    1   
1   SIMPLE  p5  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.peer_id   1   
1   SIMPLE  p4  eq_ref  PRIMARY,contract_id_idx PRIMARY 4   mabel_mindandbody_co_nz.p.ps_number_id  1   
1   SIMPLE  c   eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p4.contract_id  1   

Queriy 2:

2) [EXPLAIN] 
  SELECT p.id AS p__id, p.status_id AS p__status_id,     p.disengagement_target AS p__disengagement_target, p2.id AS p2__id, p2.name AS p2__name, p3.id AS p3__id, p3.name AS p3__name, p4.id AS p4__id, p4.ps_number AS p4__ps_number, c.id AS c__id, c.code AS c__code, p5.id AS p5__id, p5.nhi AS p5__nhi, COALESCE(p2.name, p3.name) AS p2__0 
  FROM peer_engagement p 
    LEFT JOIN person p2 ON p.assigned_psw_id = p2.id 
    LEFT JOIN person p3 ON p.waiting_psw_id = p3.id 
    INNER JOIN peer_number p4 ON p.ps_number_id = p4.id 
    INNER JOIN contract c ON p4.contract_id = c.id 
    INNER JOIN person p5 ON p.peer_id = p5.id 
  WHERE (p.is_intensive = 1 
     AND p.status_id in (0, 1, 2, 3, 4, 5, 7)) 
  LIMIT 20

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  p   range   peer_id_idx,ps_number_id_idx,status_id_idx,is_inte...   is_intensive_2  10  NULL    128 Using where
1   SIMPLE  p2  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.assigned_psw_id   1   
1   SIMPLE  p3  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.waiting_psw_id    1   
1   SIMPLE  p5  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.peer_id   1   
1   SIMPLE  p4  eq_ref  PRIMARY,contract_id_idx PRIMARY 4   mabel_mindandbody_co_nz.p.ps_number_id  1   
1   SIMPLE  c   eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p4.contract_id  1   

The difference is only in the is_intensive column.

Here is the data breakdown:

SELECT is_intensive, status_id, COUNT( * ) 
FROM  `peer_engagement` 
GROUP BY is_intensive, status_id
ORDER BY is_intensive, status_id

is_intensive    status_id   count(*)
0   1   18
0   3   229
0   4   13
0   5   101
0   6   653
0   7   252
1   1   2
1   3   26
1   5   73
1   6   14
1   7   24

To my surprise the first query is significantly sped up by index is_intensive_2, however the 2nd one is not. I would have expected the opposite: The 1st query covers over 50% of the table, so a table scan is performed, however for the 2nd query the index would be used.

I'm looking for a way to make both queries fast, i.e. in a magnitude how the first query performs now.

1

1 Answer 1

1

(is_intensive, status_id) is the optimal index. (PS: You should remove KEY(is_intensive) since it is redundant.) However, the Optimizer looks at the statistics to decide which index to use and says, roughly,...

  • If using the index would hit less than 20% of the rows, let's use it.
  • Else, why bother bouncing between the index and the data; let's eschew the index and simply scan the table.

Apparently 0 vs 1 for is_intensive makes that distinction plain.

You may find that the Optimizer makes the wrong decision. This happens because the statistics are not precise, the algorithm is not perfect, and different blocks of indexes and/or data may happen to be in cache when you run it.

2
  • is_intensive shoudl also be changed to tinyint (from the varchar(5) that is now). Commented Dec 15, 2016 at 19:39
  • Ouch! I missed that one!
    – Rick James
    Commented Dec 15, 2016 at 19:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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