-----Original Message-----
From: James W. McNeely [mailto:jmcne...@nwrads.com]
Sent: Wednesday, November 14, 2012 3:48 PM
To: mysql@lists.mysql.com
Subject: Re: query tuning
Rick,
Thanks! Here is the create table info. Sorry for the size, but you can skip
down from all the fields to see the indexes.
CREATE TABLE `exams` (
`zAddDate` date DEFAULT NULL,
`zModDate` date DEFAULT NULL,
`IdPatient` varchar(32) DEFAULT NULL,
`zModBy` varchar(255) DEFAULT NULL,
`Notes` mediumtext,
`TechComments` mediumtext,
`NameLastFirstMI` varchar(255) DEFAULT NULL,
`IdExam` varchar(32) DEFAULT NULL,
`IdAppt` varchar(255) DEFAULT NULL,
`IdProcedure` varchar(255) DEFAULT NULL,
`ProcName` mediumtext,
`zzr_StatusBy` mediumtext,
`zzr_StatusTime` mediumtext,
`zzr_StatusDate` mediumtext,
`zAddBy` varchar(255) DEFAULT NULL,
`zMasterUserTest` varchar(255) DEFAULT NULL,
`ProcAreaCode` mediumtext,
`ClinicalICD9_1` mediumtext,
`ICD9Text` mediumtext,
`FilmsStandard` mediumtext,
`FilmsAlternate` mediumtext,
`OutsideSource` mediumtext,
`ProcCode` mediumtext,
`ClinicalText` mediumtext,
`OutsideStatus` mediumtext,
`OutsideDate` varchar(255) DEFAULT NULL,
`ProcModeCode` varchar(16) DEFAULT NULL,
`DateOfBirth` varchar(255) DEFAULT NULL,
`PathDiagnosis` mediumtext,
`PathSize` mediumtext,
`PathBiopsy` mediumtext,
`PathBiopsyDate` varchar(255) DEFAULT NULL,
`ClinicalICD9_2` mediumtext,
`ClinicalICD9_3` mediumtext,
`RefPractInstructions` mediumtext,
`_External` mediumtext,
`FindCode` mediumtext,
`NWRaccession` varchar(255) DEFAULT NULL,
`Gender` varchar(255) DEFAULT NULL,
`IdOverReadFacility` varchar(255) DEFAULT NULL,
`ProcDorS` mediumtext,
`CompareToDate` varchar(255) DEFAULT NULL,
`IdRefSite` varchar(255) DEFAULT NULL,
`IsBillable` varchar(255) DEFAULT NULL,
`LastWordProcRt` mediumtext,
`IdPerformedBy` varchar(255) DEFAULT NULL,
`ContrastRiskFactors` mediumtext,
`TimeToCompletion` varchar(255) DEFAULT NULL,
`ContrastDose` mediumtext,
`OutsideSourceOther` mediumtext,
`SiteSide` mediumtext,
`SiteLocation` mediumtext,
`OverReadExamNumber` varchar(255) DEFAULT NULL,
`IsOverRead` varchar(255) DEFAULT NULL,
`OverReadCharge` varchar(255) DEFAULT NULL,
`IsBillIns` varchar(255) DEFAULT NULL,
`LastWordProcLt` mediumtext,
`IsRLB` varchar(255) DEFAULT NULL,
`IsBCHP` varchar(255) DEFAULT NULL,
`IsNWRCoordUSOverride` varchar(255) DEFAULT NULL,
`IsTechOnly` varchar(255) DEFAULT NULL,
`IsProcNotComplete` varchar(255) DEFAULT NULL,
`IsWaiverSigned` varchar(255) DEFAULT NULL,
`IdInsur1` varchar(255) DEFAULT NULL,
`IdInsur2` varchar(255) DEFAULT NULL,
`IsNoInsurance` varchar(255) DEFAULT NULL,
`IsPrintBillInfo` varchar(255) DEFAULT NULL,
`OverReadChargePrinted` varchar(255) DEFAULT NULL,
`RefSiteOther` varchar(255) DEFAULT NULL,
`IsOverReadBillIns Copy` varchar(255) DEFAULT NULL,
`ExamRoom` varchar(255) DEFAULT NULL,
`IdProtocol` varchar(255) DEFAULT NULL,
`IsTransDelayed` varchar(255) DEFAULT NULL,
`Patient Shielded` varchar(255) DEFAULT NULL,
`Patient Permitted` varchar(255) DEFAULT NULL,
`LMP Status` varchar(255) DEFAULT NULL,
`Flouro Time` varchar(255) DEFAULT NULL,
`IsWetread` varchar(255) DEFAULT NULL,
`zFlag` varchar(255) DEFAULT NULL,
`FindingFinal` mediumtext,
`IdExamsPrior` mediumtext,
`ExamDelayComment` mediumtext,
`NotesBillingAppt` mediumtext,
`ExportBatch` varchar(255) DEFAULT NULL,
`AgeRange` varchar(255) DEFAULT NULL,
`Exam Start` varchar(255) DEFAULT NULL,
`Exam Finish` varchar(255) DEFAULT NULL,
`IsNWRBillLeaseBack` varchar(255) DEFAULT NULL,
`MRIExamIndicated` varchar(255) DEFAULT NULL,
`MRIExamNegPos` varchar(255) DEFAULT NULL,
`zContinueFlag` varchar(255) DEFAULT NULL,
`IsLeaseBack` varchar(255) DEFAULT NULL,
`ContrastConcentration` mediumtext,
`NotesBillingExam` mediumtext,
`LastWordAccNum` varchar(255) DEFAULT NULL,
`LastWordExportDate` varchar(255) DEFAULT NULL,
`LastWordAcctNum` varchar(255) DEFAULT NULL,
`LastWordExportFlag` varchar(255) DEFAULT NULL,
`LeaseBackOverageKey` varchar(255) DEFAULT NULL,
`LastWordPrePost` varchar(255) DEFAULT NULL,
`LastWordExportBatch` varchar(255) DEFAULT NULL,
`IsFilmed` varchar(255) DEFAULT NULL,
`zCreateTime` varchar(255) DEFAULT NULL,
`zzr_OperICDPre` mediumtext,
`zzr_OperICDPost` mediumtext,
`ClinicalTextPostOp` mediumtext,
`zzr_OperLocation` mediumtext,
`OperTemp` varchar(255) DEFAULT NULL,
`OperPulse` varchar(255) DEFAULT NULL,
`OperBloodPressure` varchar(255) DEFAULT NULL,
`OperAirSaturation` varchar(255) DEFAULT NULL,
`zDateExamAnnual` varchar(255) DEFAULT NULL,
`OperPainLevel` varchar(255) DEFAULT NULL,
`zzr_OperSide` mediumtext,
`Surgeon` varchar(255) DEFAULT NULL,
`OperRespiration` varchar(255) DEFAULT NULL,
`OperSeriesEnd` varchar(255) DEFAULT NULL,
`OperSeriesNumber` varchar(255) DEFAULT NULL,
`zModTimestamp` datetime DEFAULT NULL,
`IdRelatedProcedure` varchar(255) DEFAULT NULL,
`ExamFailed` varchar(255) DEFAULT NULL,
`IdSurgeon` varchar(255) DEFAULT NULL,
`IsDigitized` varchar(255) DEFAULT NULL,
`CorrelatingExam` varchar(255) DEFAULT NULL,
`IdRefSiteBilling` varchar(255) DEFAULT NULL,
`IdDictation` varchar(255) DEFAULT NULL,
`zzk_organization` varchar(255) DEFAULT NULL,
`zzz_audit_trail` mediumtext,
`recp_extension` varchar(255) DEFAULT NULL,
`zzk` int(11) NOT NULL,
`is_deleted` int(11) DEFAULT NULL,
`IdReportFinal` varchar(16) DEFAULT NULL,
`dateexam` date DEFAULT NULL,
`Three_D_postprocess` mediumtext,
`Three_D_IndStation` varchar(45) DEFAULT NULL,
`statusnumber` int(11) DEFAULT NULL,
`zzz_flag` varchar(16) DEFAULT NULL,
`NotesBilling` mediumtext,
`iduser_tech` varchar(50) DEFAULT NULL,
PRIMARY KEY (`zzk`),
KEY `zzk` (`zzk`),
KEY `zModTimestamp` (`zModTimestamp`),
KEY `IdPatient` (`IdPatient`),
KEY `IdExam` (`IdExam`),
KEY `IdReportFinal` (`IdReportFinal`),
KEY `isdigitized` (`IsDigitized`),
KEY `statusnumber` (`statusnumber`),
KEY `LastwordAccNum` (`LastWordAccNum`),
KEY `zzz_flag` (`zzz_flag`),
KEY `dateexam` (`dateexam`),
KEY `IdAppt` (`IdAppt`),
KEY `ProcModeCode` (`ProcModeCode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `Appt_` (
`zAddDate` text,
`zModDate` text,
`IdPatient` varchar(32) DEFAULT NULL,
`zModBy` text,
`Notes` text,
`IdAppt` varchar(32) DEFAULT NULL,
`Room` text,
`IdProcedure` text,
`zzr_StatusBy` text,
`zzr_StatusDate` text,
`zzr_StatusTime` text,
`zAddBy` text,
`PriorSelect` text,
`ApptProcCode` text,
`ApptCaution` text,
`ApptProcDorS` text,
`ApptLenMin` text,
`WorkArea` text,
`ProcName` text,
`IdApptStart` text,
`ProcNameShort` text,
`ApptCancelNote` text,
`ApptLRB` text,
`IdPriCarePhy` varchar(30) DEFAULT NULL,
`IsWaitList` text,
`ApptIsTempWaitList` text,
`ApptDate` date DEFAULT NULL,
`zModByCode` text,
`ProcAreaModeCode` text,
`IsAnnounced` text,
`ApptTimeText` text,
`ApptTimeOut` time DEFAULT NULL,
`IdInsur1` text,
`IdInsur2` text,
`IsNoInsurance` text,
`IdRefSite` text,
`IsSendBack` text,
`IsWetRead` text,
`IdExamsPrior` text,
`NotesBilling` text,
`ApptClinicalHistory` text,
`ApptChiefComplaint` text,
`ApptCompareFilms` text,
`ApptInsCompany` text,
`ApptInsPreAuth` text,
`ApptQ1` text,
`ApptQ2` text,
`ApptQ3` text,
`ApptQ4` text,
`ApptQ5` text,
`ApptQ6` text,
`ApptQ7` text,
`ApptQ8` text,
`ApptQPatientWeight` text,
`ApptSchedBy` text,
`ConfirmMode` text,
`ApptSchedByUserId` text,
`zzr_ApptCCIdAffil` text,
`IsPriority` text,
`IsWaiverSigned` text,
`LastWordAccNum` text,
`LastWordAcctNum` text,
`LastWord` text,
`IsTechOnly` text,
`IdApptAssoc` text,
`IdFacSite` text,
`IdArea` varchar(8) DEFAULT NULL,
`zModTime` time DEFAULT NULL,
`zModTimestamp` datetime DEFAULT NULL,
`zzr_ApptCCNWR#` text,
`ApptLocation` text,
`ApptSchedTech` text,
`ApptQ9` text,
`ApptCancelCode` text,
`ApptArrivalTime` text,
`StatusInProcessSelect` text,
`IsRegence` text,
`SiteName` text,
`ProcGroup` text,
`SchedTechName_lkp` text,
`heartbeat_result` text,
`zzd_shell_result` text,
`zzk` int(11) NOT NULL,
`is_deleted` int(11) DEFAULT NULL,
`zzz_junk` text,
`is_walkin_true` text,
`zzz_audit_trail` mediumtext,
`zadddate_calc` text,
`id_patient_date` varchar(45) DEFAULT NULL,
`area_note` text,
`ApptTimeEnd` time DEFAULT NULL,
`cancel_date` datetime DEFAULT NULL,
`ICD9code` varchar(45) DEFAULT NULL,
`ApptQheartEarBrain` varchar(45) DEFAULT NULL,
`ApptQImplant` varchar(45) DEFAULT NULL,
`ApptQMetal` varchar(45) DEFAULT NULL,
`ApptQPostOp` varchar(45) DEFAULT NULL,
`ApptQPostOp6wks` varchar(45) DEFAULT NULL,
`ApptQClaustro` varchar(45) DEFAULT NULL,
`ApptQIssuesStill` varchar(45) DEFAULT NULL,
`ApptQLimitations` varchar(45) DEFAULT NULL,
`ApptQ60older` varchar(45) DEFAULT NULL,
`ApptQdiabetic` varchar(45) DEFAULT NULL,
`ApptQNephRenal` varchar(45) DEFAULT NULL,
`ApptQHypertension` varchar(45) DEFAULT NULL,
`ApptQGFRcreatine` varchar(45) DEFAULT NULL,
`ApptQContrastAllrgy` varchar(45) DEFAULT NULL,
`ApptQglucophage` varchar(45) DEFAULT NULL,
`ApptQPatientHeight` varchar(45) DEFAULT NULL,
`ApptQSentDrLtr` varchar(45) DEFAULT NULL,
`ApptQ65older` varchar(45) DEFAULT NULL,
`ApptTime_normal` varchar(45) DEFAULT NULL,
`ApptTimeRange` varchar(45) DEFAULT NULL,
`zzk_affil` varchar(45) DEFAULT NULL,
`zzk_modality` varchar(45) DEFAULT NULL,
`zzk_referral` varchar(45) DEFAULT NULL,
`zzk_resource` varchar(45) DEFAULT NULL,
`zzk_worklist` varchar(45) DEFAULT NULL,
`is_pre_double_regd` varchar(45) DEFAULT NULL,
`IdPatientDate` varchar(45) DEFAULT NULL,
`not_appt` int(11) DEFAULT NULL,
`Q01_answer` text,
`Q01_title` text,
`Q02_answer` text,
`Q02_title` text,
`Q03_answer` text,
`Q03_title` text,
`Q04_answer` text,
`Q04_title` text,
`Q05_answer` text,
`Q05_title` text,
`Q06_answer` text,
`Q06_title` text,
`Q07_answer` text,
`Q07_title` text,
`Q08_answer` text,
`Q08_title` text,
`Q09_answer` text,
`Q09_title` text,
`Q10_answer` text,
`Q10_title` text,
`Q11_answer` text,
`Q11_title` text,
`Q12_answer` text,
`Q12_title` text,
`Q13_answer` text,
`Q13_title` text,
`Q14_answer` text,
`Q14_title` text,
`Q15_answer` text,
`Q15_title` text,
`Q16_answer` text,
`Q16_title` text,
`Q17_answer` text,
`Q17_title` text,
`Q18_answer` text,
`Q18_title` text,
`Q19_answer` text,
`Q19_title` text,
`Q20_answer` text,
`Q20_title` text,
`ApptQContrstAllrgy` text,
`export` varchar(11) DEFAULT NULL,
`Q21_answer` text,
`Q22_answer` text,
`Q23_answer` text,
`Q24_answer` text,
`Q25_answer` text,
`Q26_answer` text,
`Q27_answer` text,
`Q28_answer` text,
`Q29_answer` text,
`Q30_answer` text,
`Q21_title` text,
`Q22_title` text,
`Q23_title` text,
`Q24_title` text,
`Q25_title` text,
`Q26_title` text,
`Q27_title` text,
`Q28_title` text,
`Q29_title` text,
`Q30_title` text,
`statusnumber` int(11) DEFAULT NULL,
`ranking` int(11) DEFAULT NULL,
`no_show_letter_status` varchar(45) DEFAULT NULL,
`id_sci_24` varchar(45) DEFAULT NULL,
`IdAffil_primary` varchar(45) DEFAULT NULL,
PRIMARY KEY (`zzk`),
KEY `zzk` (`zzk`),
KEY `zModTimestamp` (`zModTimestamp`),
KEY `IdPatient` (`IdPatient`),
KEY `IdAppt` (`IdAppt`),
KEY `apptdate` (`ApptDate`),
KEY `ApptTimeOut` (`ApptTimeOut`),
KEY `IdPriCarePhy` (`IdPriCarePhy`),
KEY `dont_import` (`not_appt`),
KEY `IdArea` (`IdArea`),
KEY `statusnumber` (`statusnumber`),
KEY `no_show_letter` (`no_show_letter_status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 4068352 kB'
CREATE TABLE `Affil_` (
`IdAffiliation` varchar(16) DEFAULT NULL,
`IdPract` varchar(16) DEFAULT NULL,
`Backline` varchar(16) DEFAULT NULL,
`BacklineAC` varchar(8) DEFAULT NULL,
`NWRSpeedDial` varchar(32) DEFAULT NULL,
`HospExt` varchar(16) DEFAULT NULL,
`NWMBCon` varchar(32) DEFAULT NULL,
`Email` varchar(128) DEFAULT NULL,
`PhoneAC` varchar(8) DEFAULT NULL,
`Phone` varchar(16) DEFAULT NULL,
`FaxAC` varchar(8) DEFAULT NULL,
`Fax` varchar(16) DEFAULT NULL,
`NWR#` varchar(16) DEFAULT NULL,
`PractName` varchar(64) DEFAULT NULL,
`IdAffil` varchar(30) DEFAULT NULL,
`NameLast` varchar(32) DEFAULT NULL,
`NameFirst` varchar(32) DEFAULT NULL,
`IdAffilImport` varchar(64) DEFAULT NULL,
`IsInactiveX` varchar(8) DEFAULT NULL,
`AddrSuite` varchar(128) DEFAULT NULL,
`OfficeName` varchar(255) DEFAULT NULL,
`short_name` varchar(255) DEFAULT NULL,
`NameLastXFirst` varchar(255) DEFAULT NULL,
`LocationCode` varchar(64) DEFAULT NULL,
`PagerAC` varchar(8) DEFAULT NULL,
`Pager` varchar(16) DEFAULT NULL,
`AnsSrvcAC` varchar(8) DEFAULT NULL,
`AnsSrvc` varchar(16) DEFAULT NULL,
`MCAffil` varchar(32) DEFAULT NULL,
`ReportPreference` varchar(32) DEFAULT NULL,
`ReportPrinter` varchar(32) DEFAULT NULL,
`ReportViaPACS` varchar(255) DEFAULT NULL,
`DupeMark` varchar(8) DEFAULT NULL,
`SJHPractName` varchar(128) DEFAULT NULL,
`NWR#Inactive` varchar(128) DEFAULT NULL,
`zModDate` date DEFAULT NULL,
`zModTime` time DEFAULT NULL,
`zModTimestamp` datetime DEFAULT NULL,
`IdLastword` varchar(16) DEFAULT NULL,
`zzk` int(11) NOT NULL,
`is_deleted` int(11) DEFAULT NULL,
`mysql_timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
`contact_classification` varchar(45) DEFAULT NULL,
PRIMARY KEY (`zzk`),
KEY `zzk` (`zzk`),
KEY `zModTimestamp` (`zModTimestamp`),
KEY `IdAffil` (`IdAffil`),
KEY `mysql_timestamp` (`mysql_timestamp`),
KEY `IdPract` (`IdPract`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
On Nov 14, 2012, at 3:26 PM, Rick James wrote:
Can't help without the SHOW CREATE TABLEs.
Perhaps e needs one of these:
INDEX(zzk)
INDEX(ProcModeCode, dateexam) -- in that order (I can't predict which
index it would use.)
Are IdAppt the same datatype and collation in each table?
-----Original Message-----
From: James W. McNeely [mailto:jmcne...@nwrads.com]
Sent: Wednesday, November 14, 2012 2:34 PM
To: mysql@lists.mysql.com
Subject: query tuning
I have a query I'm having trouble with. If do this query which is
DATE plus ProcModecode, it is very fast:
SELECT e.zzk FROM exams e
-- JOIN Appt_ a ON e.IdAppt = a.IdAppt
-- JOIN Affil_ af ON a.IdAffil_primary = af.IdAffil WHERE e.dateexam
= '2012-09-01' AND e.dateexam <= '2012-09-30'
AND e.ProcModeCode = 'P'
Notice that the joins are commented out.
If I do this query, which doesn't have the ProcModeCode, but does
have the IdPract across the join, it is also fast, but not as fast:
SELECT e.zzk
FROM exams e
JOIN Appt_ a ON e.IdAppt = a.IdAppt
JOIN Affil_ af ON a.IdAffil_primary = af.IdAffil WHERE e.dateexam >=
'2012- 09-01' AND e.dateexam <= '2012-09-30'
AND af.IdPract = 'D00400'
BUT, if I do this, with the ProcModeCode AND the IdPract, it is so
slow I have to kill the query:
SELECT e.zzk
FROM exams e
JOIN Appt_ a ON e.IdAppt = a.IdAppt
JOIN Affil_ af ON a.IdAffil_primary = af.IdAffil WHERE e.dateexam >=
'2012- 09-01' AND e.dateexam <= '2012-09-30' AND af.IdPract =
'D00400' AND e.ProcModeCode = 'L'
ORDER BY e.zzk DESC LIMIT 30
Here is the result of an explain on this:
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE e index dateexam,IdAppt,ProcModeCode PRIMARY 4
NULL 1121043 "Using where"
1 SIMPLE af ref IdAffil,IdPract IdPract 51 const 1
"Using where"
1 SIMPLE a ref IdAppt IdAppt 99 RIS_archive.e.IdAppt 1
"Using where"
Any ideas about how I can fix this?
TIA
Jim McNeely
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql