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
