Improve a query from Explain results

Question

I have a complex query that is dynamically assembled based upon search criteria. However, in its simplest form, it is still very slow. The main table it runs against has ~10M records. I ran an explain against a 'base' query and the first row of the explain looks bad (at least to a novice dba like me). I have read a couple tutorials about EXPLAIN, but I still am unsure how to fix the query. So, the first row of the results seems to indicate the problem, but I don't know what to do with it. I couldn't make a composite key that long even if I wanted to and some of the field names in that possible_keys column are not even in the patients table. Any help will be greatly appreciated.

   id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,patients,range,"PRIMARY,location,appt_date,status,radiologist,contract,lastname,paperwork,images_archived,hash,created,document_attached,all_images_archived,last_image_archived,modality,study_uid,company,second_access,firstname,report_delivered,ssn,order_entry_status,dob,tech,doctor,mobile_facility,accession,location_appt_date,location_created,location_lastname,ref,person_seq",location_appt_date,55,NULL,573534,"Using index condition; Using where; Using temporary; Using filesort"
1,SIMPLE,receivable_transactions,ref,patient_seq,patient_seq,4,ris-dev.patients.seq,1,NULL
1,SIMPLE,patients_dispatch,ref,patient_seq,patient_seq,4,ris-dev.patients.seq,1,NULL
1,SIMPLE,mobile_facility,ref,"unique_index,name,location",unique_index,115,"ris-dev.patients.mobile_facility,const",1,"Using where"
1,SIMPLE,mobile_facility_service_areas,eq_ref,PRIMARY,PRIMARY,4,ris-dev.mobile_facility.service_area,1,NULL

Edit: same EXPLAIN, but reformatted to be easier to read:

id select_type table                         type   possible_keys        key                key_len ref                                    rows   Extra

1  SIMPLE      patients                      range  PRIMARY              location_appt_date 55      NULL                                   573534 Using index condition; Using where; Using temporary; Using filesort
                                                    location
                                                    appt_date
                                                    status
                                                    radiologist
                                                    contract
                                                    lastname
                                                    paperwork
                                                    images_archived
                                                    hash
                                                    created
                                                    document_attached
                                                    all_images_archived
                                                    last_image_archived
                                                    modality
                                                    study_uid
                                                    company
                                                    second_access
                                                    firstname
                                                    report_delivered
                                                    ssn 
                                                    order_entry_status
                                                    dob 
                                                    tech
                                                    doctor
                                                    mobile_facility
                                                    accession
                                                    location_appt_date
                                                    location_created
                                                    location_lastname
                                                    ref 
                                                    person_seq

1  SIMPLE      receivable_transactions       ref    patient_seq          patient_seq        4       ris-dev.patients.seq                   1      NULL
1  SIMPLE      patients_dispatch             ref    patient_seq          patient_seq        4       ris-dev.patients.seq                   1      NULL
1  SIMPLE      mobile_facility               ref    unique_index         unique_index       115     ris-dev.patients.mobile_facility,const 1      Using where
                                                    name
                                                    location
1  SIMPLE      mobile_facility_service_areas eq_ref PRIMARY              PRIMARY            4       ris-dev.mobile_facility.service_area   1      NULL

The explain is setup against the following query and table structures.

    SELECT patients.fax_in_queue, patients.modality, patients.stat, patients.created, patients.seq, patients.lastname, 
patients.firstname, patients.appt_date, patients.status, patients.contract, patients.location, patients.unique_hash, 
patients.images_archived, patients.report_delivered, patients.doctor, patients.mobile_facility, patients.history, 
patients.dob, patients.all_images_archived, patients.order_entry_status, patients.tech, patients.radiologist, 
patients.last_image_archived, patients.state, patients.ss_comments, patients.completed, patients.report_status, 
patients.have_paperwork, patients.facility_room_number, patients.facility_station_name, patients.facility_bed, 
patients.findings_level, patients.document_attached, patients.study_start, patients.company, patients.accession, 
patients.number_images, patients.client_number_images, patients.sex, patients.threshhold , GROUP_CONCAT(CONCAT(CONCAT(receivable_transactions.modifier, " "), 
receivable_transactions.description) SEPARATOR ", ") AS rt_desc , patients_dispatch.seq AS doc_seq, patients_dispatch.requisition_last_sent, 
patients_dispatch.requisition_signed_by_file_seq, patients_dispatch.requisition_signed, patients_dispatch.order_reason, patients_dispatch.order_comments, 
patients_dispatch.order_taken, patients_dispatch.order_tech_last_notified, patients_dispatch.order_tech_in_transit, patients_dispatch.order_tech_in, 
patients_dispatch.order_tech_out, patients_dispatch.order_tech_ack, patients_dispatch.addr1 AS d_addr1, patients_dispatch.addr2 AS d_addr2, 
patients_dispatch.city AS d_city, patients_dispatch.state AS d_state, patients_dispatch.zip AS d_zip, CONCAT(patients.status, order_tech_out, 
order_tech_in, order_tech_in_transit) as pseudo_status , mobile_facility.requisition_fax, mobile_facility.station_list, mobile_facility.address1 as mf_addr1, 
mobile_facility.address2 as mf_addr2, mobile_facility.city as mf_city, mobile_facility.state as mf_state, mobile_facility.zip as mf_zip, 
mobile_facility.phone as mf_phone, mobile_facility.phone2 as mf_phone2, mobile_facility_service_areas.name as mf_service_area 
FROM patients LEFT JOIN receivable_transactions ON patients.seq = receivable_transactions.patient_seq 
LEFT JOIN patients_dispatch ON patients.seq = patients_dispatch.patient_seq 
LEFT JOIN mobile_facility ON patients.location = mobile_facility.location AND patients.mobile_facility = mobile_facility.name 
LEFT JOIN mobile_facility_service_areas ON mobile_facility.service_area = mobile_facility_service_areas.seq 
WHERE patients.location = "XYZCompany"  AND  ((patients.appt_date >= '2020-03-19' AND patients.appt_date <= '2020-03-19 23:59:59') 
OR (patients.appt_date <= '2020-03-19' AND patients.status < 'X')) 
GROUP BY patients.seq DESC  
ORDER BY patients.status, patients.order_entry_status,  pseudo_status, patients.order_entry_status,patients.lastname);

CREATE TABLE `patients` (
  `seq` int(11) NOT NULL AUTO_INCREMENT,
  `person_seq` int(11) NOT NULL,
  `firstname` varchar(20) NOT NULL DEFAULT '',
  `lastname` varchar(30) NOT NULL DEFAULT '',
  `middlename` varchar(20) NOT NULL DEFAULT '',
  `ref` varchar(50) NOT NULL DEFAULT '',
  `location` varchar(50) NOT NULL DEFAULT '',
  `doctor` varchar(50) NOT NULL,
  `radiologist` varchar(20) NOT NULL DEFAULT '',
  `contract` varchar(50) NOT NULL,
  `history` mediumtext NOT NULL,
  `dob` varchar(15) NOT NULL DEFAULT '0000-00-00',
  `appt_date` date NOT NULL DEFAULT '0000-00-00',
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `tech` varchar(50) NOT NULL DEFAULT '',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `ss_comments` mediumtext NOT NULL,
  `mobile_facility` varchar(60) NOT NULL DEFAULT '',
  `facility_room_number` varchar(50) NOT NULL,
  `facility_bed` varchar(20) NOT NULL,
  `facility_station_name` varchar(50) NOT NULL,
  `stat` tinyint(4) NOT NULL DEFAULT '0',
  `have_paperwork` tinyint(4) NOT NULL DEFAULT '0',
  `completed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `sex` char(1) NOT NULL DEFAULT '',
  `unique_hash` varchar(100) NOT NULL DEFAULT '',
  `number_images` int(11) NOT NULL DEFAULT '0',
  `client_number_images` int(11) NOT NULL,
  `images_archived` tinyint(4) NOT NULL DEFAULT '0',
  `completed_fax` varchar(10) NOT NULL DEFAULT '0' COMMENT 'This is the number the completed report is faxed to.',
  `report_delivered` tinyint(4) NOT NULL DEFAULT '0',
  `report_delivered_time` datetime NOT NULL,
  `document_attached` tinyint(4) NOT NULL DEFAULT '0',
  `modality` varchar(3) NOT NULL,
  `last_image_archived` datetime NOT NULL,
  `all_images_archived` tinyint(4) NOT NULL DEFAULT '0',
  `fax_in_queue` varchar(12) NOT NULL,
  `accession` varchar(100) NOT NULL,
  `study_uid` varchar(100) NOT NULL,
  `order_entry_status` tinyint(4) NOT NULL,
  `compare_to` varchar(15) NOT NULL,
  `state` varchar(3) NOT NULL,
  `company` int(11) NOT NULL,
  `second_access` varchar(50) NOT NULL,
  `threshhold` datetime NOT NULL,
  `report_status` tinyint(4) NOT NULL,
  `second_id` varchar(50) NOT NULL,
  `rad_alerted` tinyint(4) NOT NULL,
  `assigned` datetime NOT NULL,
  `findings_level` tinyint(4) NOT NULL,
  `report_viewed` tinyint(4) NOT NULL,
  `study_received` datetime NOT NULL,
  `study_start` datetime NOT NULL,
  `study_end` datetime NOT NULL,
  `completed_email` varchar(50) NOT NULL,
  `completed_send` varchar(255) NOT NULL,
  `ssn` varchar(12) NOT NULL,
  `exorder_number` varchar(30) NOT NULL,
  `exvisit_number` varchar(30) NOT NULL,
  `row_updated` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`seq`),
  KEY `location` (`location`),
  KEY `appt_date` (`appt_date`),
  KEY `status` (`status`),
  KEY `radiologist` (`radiologist`),
  KEY `contract` (`contract`),
  KEY `lastname` (`lastname`),
  KEY `paperwork` (`have_paperwork`),
  KEY `images_archived` (`images_archived`),
  KEY `hash` (`unique_hash`),
  KEY `created` (`created`),
  KEY `document_attached` (`document_attached`),
  KEY `all_images_archived` (`all_images_archived`),
  KEY `last_image_archived` (`last_image_archived`),
  KEY `modality` (`modality`),
  KEY `study_uid` (`study_uid`),
  KEY `company` (`company`),
  KEY `second_access` (`second_access`),
  KEY `firstname` (`firstname`),
  KEY `report_delivered` (`report_delivered`),
  KEY `ssn` (`ssn`),
  KEY `order_entry_status` (`order_entry_status`),
  KEY `dob` (`dob`),
  KEY `tech` (`tech`),
  KEY `doctor` (`doctor`),
  KEY `mobile_facility` (`mobile_facility`),
  KEY `accession` (`accession`),
  KEY `location_appt_date` (`location`,`appt_date`),
  KEY `location_created` (`location`,`created`),
  KEY `location_lastname` (`location`,`lastname`),
  KEY `ref` (`ref`),
  KEY `person_seq` (`person_seq`)
) ENGINE=InnoDB AUTO_INCREMENT=10242952 DEFAULT CHARSET=latin1;

CREATE TABLE `receivable_transactions` (
  `seq` int(11) NOT NULL AUTO_INCREMENT,
  `patient_seq` int(11) NOT NULL DEFAULT '0',
  `cptcode` varchar(15) NOT NULL DEFAULT '',
  `modifier` char(2) NOT NULL DEFAULT '',
  `description` varchar(100) NOT NULL DEFAULT '',
  `amount` decimal(6,2) NOT NULL DEFAULT '0.00',
  `type` char(2) NOT NULL DEFAULT '',
  `transaction` varchar(10) NOT NULL DEFAULT '',
  `radiologist` varchar(20) NOT NULL DEFAULT '',
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `completed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `report_meta_seq` int(11) NOT NULL DEFAULT '0',
  `report_header` varchar(255) NOT NULL,
  `report_body` blob NOT NULL,
  `report_impression` mediumtext NOT NULL,
  `report_hide` tinyint(4) NOT NULL,
  `radiologist_group` varchar(50) NOT NULL,
  `addendum` int(4) NOT NULL DEFAULT '0',
  `addendum_type` varchar(20) NOT NULL,
  `peer_review` int(4) NOT NULL DEFAULT '0',
  `qa_reason` varchar(255) NOT NULL DEFAULT '',
  `qa_agree` decimal(2,1) NOT NULL DEFAULT '0.0',
  `findings` tinyint(4) NOT NULL,
  `comments` mediumtext NOT NULL,
  `company` int(11) NOT NULL,
  `row_updated` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`seq`),
  KEY `patient_seq` (`patient_seq`),
  KEY `cptcode` (`cptcode`),
  KEY `transaction` (`transaction`),
  KEY `type` (`type`),
  KEY `created` (`created`),
  KEY `radiologist` (`radiologist`),
  KEY `status` (`status`),
  KEY `report_meta_seq` (`report_meta_seq`),
  KEY `Billing Check Dropdown` (`status`,`completed`),
  KEY `qa_agree` (`qa_agree`),
  KEY `peer_review` (`peer_review`),
  KEY `addendum` (`addendum`),
  KEY `company` (`company`),
  KEY `completed` (`completed`)
) ENGINE=InnoDB AUTO_INCREMENT=9380351 DEFAULT CHARSET=latin1;

CREATE TABLE `patients_dispatch` (
  `seq` int(11) NOT NULL AUTO_INCREMENT,
  `patient_seq` int(11) NOT NULL,
  `order_taken` datetime NOT NULL,
  `order_taken_by` varchar(50) NOT NULL,
  `order_person_calling` varchar(50) NOT NULL,
  `order_supervising_physician` varchar(50) NOT NULL,
  `order_trip_count` tinyint(4) NOT NULL,
  `order_trip_count_max` tinyint(4) NOT NULL,
  `order_trip_visit` tinyint(4) NOT NULL,
  `order_tech_in` datetime NOT NULL,
  `order_tech_out` datetime NOT NULL,
  `order_ssn` varchar(12) NOT NULL,
  `order_service_request_time` datetime NOT NULL,
  `order_reason` varchar(255) NOT NULL,
  `order_tech_ack` datetime NOT NULL,
  `order_tech_assigned` datetime NOT NULL,
  `order_tech_last_notified` datetime NOT NULL,
  `requisition_last_sent` datetime NOT NULL,
  `requisition_signed` datetime NOT NULL,
  `requisition_signed_by` varchar(50) NOT NULL,
  `requisition_signed_by_text` varchar(75) NOT NULL,
  `requisition_signed_by_file_seq` int(11) NOT NULL,
  `order_comments` mediumtext NOT NULL,
  `order_tech_in_transit` datetime NOT NULL,
  `fasting` tinyint(1) NOT NULL,
  `collection_time` time DEFAULT NULL,
  `addr1` varchar(100) NOT NULL,
  `addr2` varchar(100) NOT NULL,
  `city` varchar(30) NOT NULL,
  `state` varchar(3) NOT NULL,
  `zip` varchar(12) NOT NULL,
  `phone` varchar(15) NOT NULL,
  `mileage_start` int(11) NOT NULL,
  `mileage_end` int(11) NOT NULL,
  PRIMARY KEY (`seq`),
  KEY `patient_seq` (`patient_seq`)
) ENGINE=InnoDB AUTO_INCREMENT=2261091 DEFAULT CHARSET=latin1;

CREATE TABLE `mobile_facility` (
  `seq` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL,
  `location` varchar(50) DEFAULT NULL,
  `address1` varchar(50) NOT NULL,
  `address2` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(2) NOT NULL,
  `zip` varchar(10) NOT NULL,
  `phone` varchar(15) NOT NULL,
  `phone2` varchar(15) NOT NULL,
  `fax` varchar(110) NOT NULL,
  `rads_can_read` text NOT NULL,
  `rads_cant_read` text NOT NULL,
  `only_techs` text NOT NULL,
  `never_modalities` varchar(255) NOT NULL COMMENT 'A serialized list of modalities a facility may not use.',
  `station_list` mediumtext NOT NULL,
  `email` varchar(255) NOT NULL,
  `misc1` varchar(255) NOT NULL,
  `latitude` float NOT NULL DEFAULT '0',
  `longitude` float NOT NULL DEFAULT '0',
  `affiliation` int(11) NOT NULL COMMENT 'mobile_facility_affiliations seq',
  `branch` int(11) NOT NULL COMMENT 'mobile_facility_branches seq',
  `service_area` int(11) NOT NULL COMMENT 'mobile_facility_service_areas seq',
  `other_id` varchar(50) NOT NULL COMMENT 'Usually used for HL7',
  `facility_type` varchar(2) DEFAULT NULL,
  `no_stat` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Should the facility allow stat priority on patients?',
  `facility_notes` varchar(512) DEFAULT NULL,
  `requisition_fax` varchar(110) NOT NULL,
  `report_template` text NOT NULL,
  `all_orders_stat` tinyint(1) NOT NULL,
  `sms_notification` varchar(15) NOT NULL,
  `tat` varchar(10) NOT NULL,
  `npi` varchar(15) NOT NULL,
  `NMXR` tinyint(4) NOT NULL DEFAULT '0',
  `billing_type` varchar(10) NOT NULL,
  `salesman` varchar(75) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `default_bill_to` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`seq`),
  UNIQUE KEY `unique_index` (`name`,`location`),
  KEY `name` (`name`),
  KEY `location` (`location`)
) ENGINE=InnoDB AUTO_INCREMENT=155104 DEFAULT CHARSET=latin1;

CREATE TABLE `mobile_facility_service_areas` (
  `seq` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `location` varchar(50) NOT NULL,
  PRIMARY KEY (`seq`)
) ENGINE=InnoDB AUTO_INCREMENT=841 DEFAULT CHARSET=latin1;

Answer 1

Let me focus on the part that affects optimization the most:

    FROM  patients AS p
    LEFT JOIN  receivable_transactions AS rt  ON p.seq = rt.patient_seq
    LEFT JOIN  patients_dispatch AS pd  ON p.seq = pd.patient_seq
    LEFT JOIN  mobile_facility AS mf  ON p.location = mf.location
      AND  p.mobile_facility AS mf = mf.name
    LEFT JOIN  mobile_facility_service_areas AS sa  ON mf.service_area = sa.seq
    WHERE  p.location = "XYZCompany"
      AND  ((p.appt_date >= '2020-03-19'
                      AND  p.appt_date <= '2020-03-19 23:59:59')
              OR  (p.appt_date <= '2020-03-19'
                      AND  p.status < 'X')
           )
    GROUP BY  p.seq DESC
    ORDER BY  p.status, p.order_entry_status, pseudo_status, p.order_entry_status,
        p.lastname);

The biggest issue is the OR. It often prevents most optimizations. The usual fix is to turn it into a UNION:

( SELECT ...
    FROM .. JOIN ..
    WHERE p.location = "XYZCompany"
      AND  p.appt_date >= '2020-03-19'
      AND  p.appt_date  < '2020-03-19' + INTERVAL 1 DAY
   ... 
)
UNION ALL
( SELECT ...
    FROM .. JOIN ..
    WHERE p.location = "XYZCompany"
      AND  p.appt_date <= '2020-03-19'
      AND  p.status < 'X'
   ... 
)

Each select can benefit from this composite index on patients:

(location, appt_date, status)

The < 'X' is problematic because two ranges (appt_date and status) cannot both be used effectively. What are the possible values of status? If there is only one value before 'X', say 'M', then this would be much better: p.status = 'M' together with another index: (location, status, appt_date)

SELECT lots of stuff, then GROUP BY p.seq -- This is probably create strange results. (Search for ONLY_FULL_GROUP_BY for more discussion). It may be better to first get the patients.seq values (since that is all you are filtering on), then join to the other tables. This would eliminate the GROUP BY, or at least force you to deal with which row to fetch from each of the other tables.

range location_appt_date 55 573534 Using index condition; Using where; Using temporary; Using filesort -- says

• 55 = 2+50 (for varchar(50)) + 3 (for date) -- neither is NULL.
• Based on the 55, I wonder if it is so well optimized that the OR->UNION is not needed.
• "Using index condition" is internally called ICP (Index Condition Pushdown) if you want further understanding.
• "Using filesort" may be an understatement -- There are probably two sorts, one for GROUP BY, one for ORDER BY. EXPLAIN FORMAT=JSON SELECT ... would make it clear. (And hence my hint that the GROUP BY should be avoided.

You have some redundant indexes (not relevant to much other than disk space): INDEX(a,b), INDEX(a) --> toss INDEX(a).

patients has an awful number of indexes.

The other tables seem to have adequate indexes for your query.

Answer 2

It's only using the index on location, but that only narrows down the search to about a half a million rows. You'd like it to use an index to further narrow down by the appt_date.

However, the use of OR in your WHERE clause is causing a problem. It can't decide how to use the index.

Here's what I suggest:

• Drop the index on location because it's redundant with the other indexes that have location as their first column.

• Replace the index on location_appt_date with an index on location_appt_date_status.

  ALTER TABLE patients
    DROP KEY location,
    DROP KEY location_appt_date,
    ADD KEY location_appt_date_status (location, appt_date, status);
  

• Refactor the query to use UNION instead of OR:

  SELECT ... (all the columns you have) ... 
  FROM (
          SELECT * FROM patients USE INDEX (location_appt_date_status)
          WHERE location = 'XYZCompany' AND appt_date >= '2020-03-19' AND appt_date < '2020-03-20'
          UNION 
          SELECT * FROM patients USE INDEX (location_appt_date_status)
          WHERE location = 'XYZCompany' AND appt_date <= '2020-03-19' AND status < 'X'
  ) AS p
  LEFT JOIN receivable_transactions FORCE INDEX (patient_seq) 
    ON p.seq = receivable_transactions.patient_seq
  LEFT JOIN patients_dispatch FORCE INDEX (patient_seq) 
    ON p.seq = patients_dispatch.patient_seq
  INNER JOIN mobile_facility FORCE INDEX (unique_index) 
    ON p.location = mobile_facility.location AND p.mobile_facility = mobile_facility.name
  INNER JOIN mobile_facility_service_areas FORCE INDEX (PRIMARY) 
    ON mobile_facility.service_area = mobile_facility_service_areas.seq
  GROUP BY p.seq 
  ORDER BY p.status, p.order_entry_status, pseudo_status, p.order_entry_status, p.lastname
  

You might not need all the USE INDEX() / FORCE INDEX() optimizer hints I used. I did those because I was testing with empty tables, and that can confuse the optimizer.