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
