List:General Discussion« Previous MessageNext Message »
From:Rick James Date:November 15 2012 1:12am
Subject:RE: query tuning
View as plain text  
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

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