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
