Rick,
This is incredibly useful, thanks for taking the time to share with me. I am really
learning stuff here!
Thanks,
Jim
On Nov 14, 2012, at 5:12 PM, Rick James wrote:
> 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:jmcneely@stripped]
>> Sent: Wednesday, November 14, 2012 3:48 PM
>> To: mysql@stripped
>> 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: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
>>>
>>
>>
>> --
>> 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
>
