From: Rick James
Date: August 23 2012 7:26pm
Subject: RE: view query is slow
List-Archive: http://lists.mysql.com/mysql/228066
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148A047BE7@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

That is an example of where VIEWs screw up optimizations.
Rumor has it that 5.6.6 might have improvements.

Probably inefficient:
  ON CONCAT(e.IdAppt , '0') =3D c.IdApptType

p might benefit from
  INDEX(AddrState, DateOfBirth)

SHOW CREATE TABLE (for each table)
EXPLAIN SELECT (with and without VIEW)
We might have more comments/suggestions.

> -----Original Message-----
> From: James W. McNeely [mailto:jim@stripped]
> Sent: Thursday, August 23, 2012 11:30 AM
> To: mysql@stripped
> Subject: view query is slow
>=20
> I am working on a view based on this query:
>=20
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> SELECT
> -- Patient Info
> p.IdPatient,
> p.IdLastword MRN,
> p.NameLast,
> p.NameFirst,
> p.Addr1,
> p.Addr2,
> p.AddrCity,
> p.AddrState,
> p.AddrZip,
> p.Gender,
> p.DateOfBirth,
> -- Provider Info
> af.IdAffil,
> af.PractName,
> af.OfficeName,
> -- Exam Info
> e.IdExam,
> e.dateexam,
> a.WorkArea dept,
> a.Room location,
> e.ProcModeCode,
> e.ProcName,
> e.IdRefSite,
> ec.IdCPT,
> e.zzk exam_zzk,
> ec.zzk examcpt_zzk
> FROM patient_ p
> LEFT JOIN exams e ON e.IdPatient =3D p.IdPatient LEFT JOIN Examcpt_ ec ON
> (e.IdExam =3D ec.IdExam AND '1' =3D Quantity) LEFT JOIN Copy_ c ON
> CONCAT(e.IdAppt , '0') =3D c.IdApptType LEFT JOIN Appt_ a ON e.IdAppt =3D
> a.IdAppt LEFT JOIN Affil_ af ON c.IdPractAffil =3D af.IdAffil WHERE
> p.AddrState =3D 'WA'
> AND e.statusnumber =3D '4'
> AND e.IdRefSite <> 'S50'
> AND e.IdRefSite <> 'S51'
> AND e.IdREfSite <> 'S63'
> AND p.DateOfBirth < DATE_ADD(CURDATE(), INTERVAL '-2' MONTH) AND a.zzk
> IS NOT NULL =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>=20
> If I run this query itself (not in the view), and add this:
>=20
> AND e.dateexam =3D '2012-08-13'
>=20
> it runs like lightning, super fast. But if I run the query against the
> view, for example "SELECT * FROM exam_view WHERE dateexam =3D '2012-08-
> 13'
>=20
> It is so glacially slow that I end up having to kill the query. What is
> going on, and how can I fix this?
>=20
> Jim McNeely
> Northwest Radiologists
> Senior Database Programmer
> 360-788-9022 desk
> 360-303-3332 mobile
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql