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: > > 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
