List:General Discussion« Previous MessageNext Message »
From:Iñigo Medina Date:November 15 2012 7:30pm
Subject:RE: query tuning
View as plain text  
Good advices indeed. Thanks for sharing.

iñ

On Wed, 14 Nov 2012, 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