A PRIMARY KEY is a KEY, so the second of these is redundant: > PRIMARY KEY (`zzk`), > KEY `zzk` (`zzk`),
"Compound" indexes are your friend. Learn from http://mysql.rjweb.org/doc.php/index1 > `zzr_StatusTime` mediumtext, > `zzr_StatusDate` mediumtext, Don't need 16MB for a date or time. Combining the fields is usually a better idea. VARCHAR(255), and especially TEXT/MEDIUMTEXT, can hurt performance when temp tables needed in a query -- use something sensible. > `NameLastFirstMI` varchar(255) DEFAULT NULL, 1 letter? Occupying up to 767 bytes (255 utf8 chars)! InnoDB is generally faster than MyISAM. > KEY `isdigitized` (`IsDigitized`), Rarely is a 'flag' (yes/no) value worth indexing. > `is_deleted` int(11) DEFAULT NULL, If it is just a 0/1 flag, then consider TINYINT UNSIGNED NOT NULL -- it will shrink the space for that field significantly. > `Q01_answer` text, > `Q01_title` text, > `Q02_answer` text, > `Q02_title` text, Generally better to have another table for "arrays"; in this case it might have 4 columns: Appt_zzk, question_number, answer, title. and multiple rows for each Appt. > ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 4068352 kB' Sounds confused about the engine?? Most fields are NULLable. Not a serious issue, but not good practice. As you add INDEX(ProcModeCode, dateexam) you can DROP this as being redundant: KEY `ProcModeCode` (`ProcModeCode`) The quick fix is to try the index suggested. It should change from > 1 SIMPLE e index dateexam,IdAppt,ProcModeCode PRIMARY 4 > NULL 1121043 "Using where" Note the huge estimated number of rows. That should decline significantly. What's the value of key_buffer_size? It should generally be 20% of _available_ RAM (for MyISAM usage). > -----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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
