List:General Discussion« Previous MessageNext Message »
From:James W. McNeely Date:November 14 2012 11:47pm
Subject:Re: query tuning
View as plain text  
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:jmcneely@stripped]
>> Sent: Wednesday, November 14, 2012 2:34 PM
>> To: mysql@stripped
>> 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
> 

Thread
query tuningJames W. McNeely14 Nov
  • RE: query tuningRick James14 Nov
    • Re: query tuningJames W. McNeely14 Nov
      • RE: query tuningRick James15 Nov
        • Re: query tuningJames W. McNeely15 Nov
        • RE: query tuningIƱigo Medina15 Nov