Re: query tuning

2012-11-15 Thread James W. McNeely 
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,ProcModeCodePRIMARY 4  
  NULL1121043 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:jmcne...@nwrads.com]
 Sent: Wednesday, November 14, 2012 3:48 PM
 To: mysql@lists.mysql.com
 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

RE: query tuning

2012-11-15 Thread Iñigo Medina 


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,ProcModeCodePRIMARY 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:jmcne...@nwrads.com]
Sent: Wednesday, November 14, 2012 3:48 PM
To: mysql@lists.mysql.com
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

RE: query tuning

2012-11-14 Thread Rick James 
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:jmcne...@nwrads.com]
 Sent: Wednesday, November 14, 2012 2:34 PM
 To: mysql@lists.mysql.com
 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:
 
 idselect_type table   typepossible_keys   key key_len ref
   rowsExtra
 1 SIMPLE  e   index   dateexam,IdAppt,ProcModeCodePRIMARY 4
   NULL1121043 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

Re: query tuning

2012-11-14 Thread James W. McNeely 
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`

RE: query tuning

2012-11-14 Thread Rick James 
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,ProcModeCodePRIMARY 4   
 NULL1121043 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:jmcne...@nwrads.com]
 Sent: Wednesday, November 14, 2012 3:48 PM
 To: mysql@lists.mysql.com
 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