List:General Discussion« Previous MessageNext Message »
From:James W. McNeely Date:November 15 2012 4:28pm
Subject:Re: query tuning
View as plain text  
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
> 

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