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
