Help with slow query

I am trying to set up an export query which is executing very slowly, =
and I was hoping I could get some help. Here is the query:

SELECT a.IdAppt, a.IdPatient,=20
p.NameLast, p.NameFirst, p.NameMI,=20
a.IdProcedure, a.ProcName, CAST(CONCAT(a.ApptDate, " ", a.ApptTimeOut) =
AS CHAR)=20
ApptDateTime, a.ApptLenMin Duration,
a.Room Primary_Resource_ID, 'R' as Primary_Resource_Type,=20
t.Note, c.IdPractAffil DoctorID, af.PractName DoctorName, a.ICD9code =
ICD9,=20
'??????' Diagnosis_free_test

from Appt_ a LEFT JOIN (patient_ p, today_ t, Copy_ c, Affil_ af)=20
ON (a.IdPatient =3D p.IdPatient=20
AND a.IdPatientDate =3D t.IdPatientDate=20
AND CONCAT(a.IdAppt, '0') =3D c.IdApptType=20
AND a.IdPriCarePhy =3D af.IdAffil)
WHERE a.ApptDate >=3D '2009-03-01';=20

p.IdPatient, t.IdPatientDate, c.IdApptType, and af.IdAffil are all =
indexed. Also I selectively took out join parameters until there was =
nothing but a join on the patient table, and it was still slow, but when =
I took that out, the query was extremely fast. What might I be doing =
wrong?

Thanks,

Jim McNeely=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Help with slow query

Hi Jim,

On 3/9/2011 17:57, Jim McNeely wrote:
> I am trying to set up an export query which is executing very slowly, and I was hoping I could get some help. Here is the query:
>
> SELECT a.IdAppt, a.IdPatient,
> p.NameLast, p.NameFirst, p.NameMI,
> a.IdProcedure, a.ProcName, CAST(CONCAT(a.ApptDate, " ", a.ApptTimeOut) AS CHAR)
> ApptDateTime, a.ApptLenMin Duration,
> a.Room Primary_Resource_ID, 'R' as Primary_Resource_Type,
> t.Note, c.IdPractAffil DoctorID, af.PractName DoctorName, a.ICD9code ICD9,
> '??????' Diagnosis_free_test
>
> from Appt_ a LEFT JOIN (patient_ p, today_ t, Copy_ c, Affil_ af)
> ON (a.IdPatient = p.IdPatient
> AND a.IdPatientDate = t.IdPatientDate
> AND CONCAT(a.IdAppt, '0') = c.IdApptType
> AND a.IdPriCarePhy = af.IdAffil)
> WHERE a.ApptDate>= '2009-03-01';
>
> p.IdPatient, t.IdPatientDate, c.IdApptType, and af.IdAffil are all indexed. Also I selectively took out join parameters until there was nothing but a join on the patient table, and it was still slow, but when I took that out, the query was extremely fast. What might I be doing wrong?
>
> Thanks,
>
> Jim McNeely

The performance problem is with your Cartesian product. I think you
meant to write:

from Appt_ a
LEFT JOIN patient_ p
ON a.IdPatient = p.IdPatient
LEFT JOIN today_ t
ON a.IdPatientDate = t.IdPatientDate
LEFT JOIN Copy_ c
ON CONCAT(a.IdAppt, '0') = c.IdApptType
LEFT JOIN Affil_ af
ON a.IdPriCarePhy = af.IdAffil

As of 5.0.12, the comma operator for table joins was demoted in the
'order of precedence' for query execution. That means that MySQL became
more complaint with the SQL standard but it also means that using a
comma-join instead of an explicit ANSI join can result in a Cartesian
product more frequently.

Try my style and compare how it works. If both styles are similarly
slow, collect the EXPLAIN plan for this query and share with the list.

Yours,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Help with slow query

--Apple-Mail-1--654647207
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=us-ascii

Shawn,

Thanks for the great help! It still is not working. I did an EXPLAIN on =
this query with your amended split out join statements and got this:

=
+----+-------------+-------+-------+---------------+-------- ----+---------=
+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | =
key_len | ref | rows | Extra |
=
+----+-------------+-------+-------+---------------+-------- ----+---------=
+------+--------+-------------+
| 1 | SIMPLE | a | range | apptdate | apptdate | 4 =
| NULL | 296148 | Using where |
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL =
| NULL | 262462 | |
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL =
| NULL | 311152 | |
| 1 | SIMPLE | c | ref | IdApptType | IdApptType | 51 =
| func | 1 | |
| 1 | SIMPLE | af | ALL | NULL | NULL | NULL =
| NULL | 5680 | |
=
+----+-------------+-------+-------+---------------+-------- ----+---------=
+------+--------+-------------+

What I'm not catching is why it says there is no key it can use for the =
patient table; here is a portion of the show create:

PRIMARY KEY (`zzk`),
KEY `IdPatient` (`IdPatient`),
KEY `SSN` (`SSN`),
KEY `IdLastword` (`IdLastword`),
KEY `DOB` (`DateOfBirth`),
KEY `NameFirst` (`NameFirst`),
KEY `NameLast` (`NameLast`)

So, the IdPatient is at least a POSSIBLE key, right?

On Mar 9, 2011, at 8:34 PM, Shawn Green (MySQL) wrote:

> Hi Jim,
>=20
> On 3/9/2011 17:57, Jim McNeely wrote:
>> I am trying to set up an export query which is executing very slowly, =
and I was hoping I could get some help. Here is the query:
>>=20
>> SELECT a.IdAppt, a.IdPatient,
>> p.NameLast, p.NameFirst, p.NameMI,
>> a.IdProcedure, a.ProcName, CAST(CONCAT(a.ApptDate, " ", =
a.ApptTimeOut) AS CHAR)
>> ApptDateTime, a.ApptLenMin Duration,
>> a.Room Primary_Resource_ID, 'R' as Primary_Resource_Type,
>> t.Note, c.IdPractAffil DoctorID, af.PractName DoctorName, a.ICD9code =
ICD9,
>> '??????' Diagnosis_free_test
>>=20
>> from Appt_ a LEFT JOIN (patient_ p, today_ t, Copy_ c, Affil_ af)
>> ON (a.IdPatient =3D p.IdPatient
>> AND a.IdPatientDate =3D t.IdPatientDate
>> AND CONCAT(a.IdAppt, '0') =3D c.IdApptType
>> AND a.IdPriCarePhy =3D af.IdAffil)
>> WHERE a.ApptDate>=3D '2009-03-01';
>>=20
>> p.IdPatient, t.IdPatientDate, c.IdApptType, and af.IdAffil are all =
indexed. Also I selectively took out join parameters until there was =
nothing but a join on the patient table, and it was still slow, but when =
I took that out, the query was extremely fast. What might I be doing =
wrong?
>>=20
>> Thanks,
>>=20
>> Jim McNeely
>=20
> The performance problem is with your Cartesian product. I think you =
meant to write:
>=20
> from Appt_ a
> LEFT JOIN patient_ p
> ON a.IdPatient =3D p.IdPatient
> LEFT JOIN today_ t
> ON a.IdPatientDate =3D t.IdPatientDate
> LEFT JOIN Copy_ c
> ON CONCAT(a.IdAppt, '0') =3D c.IdApptType
> LEFT JOIN Affil_ af
> ON a.IdPriCarePhy =3D af.IdAffil
>=20
> As of 5.0.12, the comma operator for table joins was demoted in the =
'order of precedence' for query execution. That means that MySQL became =
more complaint with the SQL standard but it also means that using a =
comma-join instead of an explicit ANSI join can result in a Cartesian =
product more frequently.
>=20
> Try my style and compare how it works. If both styles are similarly =
slow, collect the EXPLAIN plan for this query and share with the list.
>=20
> Yours,
> --=20
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Djim@newcenturydata.com
>=20


--Apple-Mail-1--654647207--

Re: Help with slow query

--Apple-Mail-2--651375834
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=us-ascii

Rhino,

Thanks for the help and time! Actually, I thought the same thing, but =
what's weird is that is the only thing that doesn't slow it down. If I =
take out all of the join clauses EXCEPT that one the query runs =
virtually instantaneously. for some reason it will use the index in that =
case and it works. If I take out everything like this:

SELECT a.IdAppt, a.IdPatient,=20
p.NameLast, p.NameFirst, p.NameMI

from Appt_ a
LEFT JOIN patient_ p
ON a.IdPatient =3D p.IdPatient
WHERE a.ApptDate >=3D '2009-03-01';

It is still utterly slow. EXPLAIN looks like this:

=
+----+-------------+-------+-------+---------------+-------- --+---------+-=
-----+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len =
| ref | rows | Extra |
=
+----+-------------+-------+-------+---------------+-------- --+---------+-=
-----+--------+-------------+
| 1 | SIMPLE | a | range | apptdate | apptdate | 4 =
| NULL | 296166 | Using where |
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL =
| NULL | 262465 | |
=
+----+-------------+-------+-------+---------------+-------- --+---------+-=
-----+--------+-------------+

But, very good try. I thought this might be it as well.

Thanks,

Jim McNeely

On Mar 10, 2011, at 9:05 AM, Rhino wrote:

>=20
> What I'm about to say may be completely out to lunch so don't be =
afraid to dismiss it. I'm more a DB2 guy than a MySQL guy and I'm =
getting rusty on both but I've always been struck by how similar the two =
dtabases are. Therefore, I want to offer an insight on why this query =
would not perform terribly well in DB2. I simply don't know if it is =
applicable to MySQL.
>=20
> In DB2, using functions on predicates (conditions in a WHERE clause), =
prevents DB2 from using an index to satisfy that predicate. (Or at least =
it used to: I'm not certain if that has been remedied in recent versions =
of the DB2 optimizer.) Therefore, the CONCAT() function in the line
> "AND CONCAT(a.IdAppt, '0') =3D c.IdApptType" would ensure that no =
index on the IdAppt column would be used to find the rows of the table =
that satisfied that condition.
>=20
> My suggestion is that you try rewriting that condition to avoid using =
CONCAT() - or any other function - and see if that helps the performance =
of your query. That would require modifying your data to append a zero =
to the end of the existing date in IdApptType column, which may or may =
not be a reasonable thing to do. You'll have to decide about that.
>=20
> Again, I could be all wet here so don't have me killed if I'm wrong =
about this :-) I'm just trying to help ;-)
>=20
> --
> Rhino
>=20
> On 2011-03-10 11:38, Jim McNeely wrote:
>> Shawn,
>>=20
>> Thanks for the great help! It still is not working. I did an EXPLAIN =
on this query with your amended split out join statements and got this:
>>=20
>> =
+----+-------------+-------+-------+---------------+-------- ----+---------=
+------+--------+-------------+
>> | id | select_type | table | type | possible_keys | key | =
key_len | ref | rows | Extra |
>> =
+----+-------------+-------+-------+---------------+-------- ----+---------=
+------+--------+-------------+
>> | 1 | SIMPLE | a | range | apptdate | apptdate | 4 =
| NULL | 296148 | Using where |
>> | 1 | SIMPLE | p | ALL | NULL | NULL | =
NULL | NULL | 262462 | |
>> | 1 | SIMPLE | t | ALL | NULL | NULL | =
NULL | NULL | 311152 | |
>> | 1 | SIMPLE | c | ref | IdApptType | IdApptType | 51 =
| func | 1 | |
>> | 1 | SIMPLE | af | ALL | NULL | NULL | =
NULL | NULL | 5680 | |
>> =
+----+-------------+-------+-------+---------------+-------- ----+---------=
+------+--------+-------------+
>>=20
>> What I'm not catching is why it says there is no key it can use for =
the patient table; here is a portion of the show create:
>>=20
>> PRIMARY KEY (`zzk`),
>> KEY `IdPatient` (`IdPatient`),
>> KEY `SSN` (`SSN`),
>> KEY `IdLastword` (`IdLastword`),
>> KEY `DOB` (`DateOfBirth`),
>> KEY `NameFirst` (`NameFirst`),
>> KEY `NameLast` (`NameLast`)
>>=20
>> So, the IdPatient is at least a POSSIBLE key, right?
>>=20
>> On Mar 9, 2011, at 8:34 PM, Shawn Green (MySQL) wrote:
>>=20
>>> Hi Jim,
>>>=20
>>> On 3/9/2011 17:57, Jim McNeely wrote:
>>>> I am trying to set up an export query which is executing very =
slowly, and I was hoping I could get some help. Here is the query:
>>>>=20
>>>> SELECT a.IdAppt, a.IdPatient,
>>>> p.NameLast, p.NameFirst, p.NameMI,
>>>> a.IdProcedure, a.ProcName, CAST(CONCAT(a.ApptDate, " ", =
a.ApptTimeOut) AS CHAR)
>>>> ApptDateTime, a.ApptLenMin Duration,
>>>> a.Room Primary_Resource_ID, 'R' as Primary_Resource_Type,
>>>> t.Note, c.IdPractAffil DoctorID, af.PractName DoctorName, =
a.ICD9code ICD9,
>>>> '??????' Diagnosis_free_test
>>>>=20
>>>> from Appt_ a LEFT JOIN (patient_ p, today_ t, Copy_ c, Affil_ af)
>>>> ON (a.IdPatient =3D p.IdPatient
>>>> AND a.IdPatientDate =3D t.IdPatientDate
>>>> AND CONCAT(a.IdAppt, '0') =3D c.IdApptType
>>>> AND a.IdPriCarePhy =3D af.IdAffil)
>>>> WHERE a.ApptDate>=3D '2009-03-01';
>>>>=20
>>>> p.IdPatient, t.IdPatientDate, c.IdApptType, and af.IdAffil are all =
indexed. Also I selectively took out join parameters until there was =
nothing but a join on the patient table, and it was still slow, but when =
I took that out, the query was extremely fast. What might I be doing =
wrong?
>>>>=20
>>>> Thanks,
>>>>=20
>>>> Jim McNeely
>>> The performance problem is with your Cartesian product. I think you =
meant to write:
>>>=20
>>> from Appt_ a
>>> LEFT JOIN patient_ p
>>> ON a.IdPatient =3D p.IdPatient
>>> LEFT JOIN today_ t
>>> ON a.IdPatientDate =3D t.IdPatientDate
>>> LEFT JOIN Copy_ c
>>> ON CONCAT(a.IdAppt, '0') =3D c.IdApptType
>>> LEFT JOIN Affil_ af
>>> ON a.IdPriCarePhy =3D af.IdAffil
>>>=20
>>> As of 5.0.12, the comma operator for table joins was demoted in the =
'order of precedence' for query execution. That means that MySQL became =
more complaint with the SQL standard but it also means that using a =
comma-join instead of an explicit ANSI join can result in a Cartesian =
product more frequently.
>>>=20
>>> Try my style and compare how it works. If both styles are similarly =
slow, collect the EXPLAIN plan for this query and share with the list.
>>>=20
>>> Yours,
>>> --=20
>>> Shawn Green
>>> MySQL Principal Technical Support Engineer
>>> Oracle USA, Inc. - Hardware and Software, Engineered to Work =
Together.
>>> Office: Blountville, TN
>>>=20
>>> --=20
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Djim@newcenturydata.com
>>>=20
>>=20


--Apple-Mail-2--651375834--

Re: Help with slow query

If the optimizer chooses the wrong index, you can tell it what index to use.

SELECT a.IdAppt, a.IdPatient,
p.NameLast, p.NameFirst, p.NameMI

from Appt_ a force index(id_patient)
LEFT JOIN patient_ p
ON a.IdPatient = p.IdPatient

WHERE a.ApptDate >= '2009-03-01';

See http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

Mike

At 11:32 AM 3/10/2011, Jim McNeely wrote:
>Rhino,
>
>Thanks for the help and time! Actually, I thought the same thing, but
>what's weird is that is the only thing that doesn't slow it down. If I
>take out all of the join clauses EXCEPT that one the query runs virtually
>instantaneously. for some reason it will use the index in that case and it
>works. If I take out everything like this:
>
>SELECT a.IdAppt, a.IdPatient,
>p.NameLast, p.NameFirst, p.NameMI
>
>from Appt_ a
>LEFT JOIN patient_ p
> ON a.IdPatient = p.IdPatient
>WHERE a.ApptDate >= '2009-03-01';
>
>It is still utterly slow. EXPLAIN looks like this:
>
>+----+-------------+-------+-------+---------------+------- ---+---------+------+--------+-------------+
>| id | select_type | table | type | possible_keys | key | key_len |
>ref | rows | Extra |
>+----+-------------+-------+-------+---------------+------- ---+---------+------+--------+-------------+
>| 1 | SIMPLE | a | range | apptdate | apptdate | 4 |
>NULL | 296166 | Using where |
>| 1 | SIMPLE | p | ALL | NULL | NULL | NULL |
>NULL | 262465 | |
>+----+-------------+-------+-------+---------------+------- ---+---------+------+--------+-------------+
>
>But, very good try. I thought this might be it as well.
>
>Thanks,
>
>Jim McNeely
>
>On Mar 10, 2011, at 9:05 AM, Rhino wrote:
>
> >
> > What I'm about to say may be completely out to lunch so don't be afraid
> to dismiss it. I'm more a DB2 guy than a MySQL guy and I'm getting rusty
> on both but I've always been struck by how similar the two dtabases are.
> Therefore, I want to offer an insight on why this query would not perform
> terribly well in DB2. I simply don't know if it is applicable to MySQL.
> >
> > In DB2, using functions on predicates (conditions in a WHERE clause),
> prevents DB2 from using an index to satisfy that predicate. (Or at least
> it used to: I'm not certain if that has been remedied in recent versions
> of the DB2 optimizer.) Therefore, the CONCAT() function in the line
> > "AND CONCAT(a.IdAppt, '0') = c.IdApptType" would ensure that no index
> on the IdAppt column would be used to find the rows of the table that
> satisfied that condition.
> >
> > My suggestion is that you try rewriting that condition to avoid using
> CONCAT() - or any other function - and see if that helps the performance
> of your query. That would require modifying your data to append a zero to
> the end of the existing date in IdApptType column, which may or may not
> be a reasonable thing to do. You'll have to decide about that.
> >
> > Again, I could be all wet here so don't have me killed if I'm wrong
> about this :-) I'm just trying to help ;-)
> >
> > --
> > Rhino
> >
> > On 2011-03-10 11:38, Jim McNeely wrote:
> >> Shawn,
> >>
> >> Thanks for the great help! It still is not working. I did an EXPLAIN
> on this query with your amended split out join statements and got this:
> >>
> >>
> +----+-------------+-------+-------+---------------+-------- ----+---------+------+--------+-------------+
> >> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
> >>
> +----+-------------+-------+-------+---------------+-------- ----+---------+------+--------+-------------+
> >> | 1 | SIMPLE | a | range | apptdate | apptdate |
> 4 | NULL | 296148 | Using where |
> >> | 1 | SIMPLE | p | ALL | NULL | NULL |
> NULL | NULL | 262462 | |
> >> | 1 | SIMPLE | t | ALL | NULL | NULL |
> NULL | NULL | 311152 | |
> >> | 1 | SIMPLE | c | ref | IdApptType | IdApptType |
> 51 | func | 1 | |
> >> | 1 | SIMPLE | af | ALL | NULL | NULL |
> NULL | NULL | 5680 | |
> >>
> +----+-------------+-------+-------+---------------+-------- ----+---------+------+--------+-------------+
> >>
> >> What I'm not catching is why it says there is no key it can use for
> the patient table; here is a portion of the show create:
> >>
> >> PRIMARY KEY (`zzk`),
> >> KEY `IdPatient` (`IdPatient`),
> >> KEY `SSN` (`SSN`),
> >> KEY `IdLastword` (`IdLastword`),
> >> KEY `DOB` (`DateOfBirth`),
> >> KEY `NameFirst` (`NameFirst`),
> >> KEY `NameLast` (`NameLast`)
> >>
> >> So, the IdPatient is at least a POSSIBLE key, right?
> >>
> >> On Mar 9, 2011, at 8:34 PM, Shawn Green (MySQL) wrote:
> >>
> >>> Hi Jim,
> >>>
> >>> On 3/9/2011 17:57, Jim McNeely wrote:
> >>>> I am trying to set up an export query which is executing very
> slowly, and I was hoping I could get some help. Here is the query:
> >>>>
> >>>> SELECT a.IdAppt, a.IdPatient,
> >>>> p.NameLast, p.NameFirst, p.NameMI,
> >>>> a.IdProcedure, a.ProcName, CAST(CONCAT(a.ApptDate, " ",
> a.ApptTimeOut) AS CHAR)
> >>>> ApptDateTime, a.ApptLenMin Duration,
> >>>> a.Room Primary_Resource_ID, 'R' as Primary_Resource_Type,
> >>>> t.Note, c.IdPractAffil DoctorID, af.PractName DoctorName, a.ICD9code
> ICD9,
> >>>> '??????' Diagnosis_free_test
> >>>>
> >>>> from Appt_ a LEFT JOIN (patient_ p, today_ t, Copy_ c, Affil_ af)
> >>>> ON (a.IdPatient = p.IdPatient
> >>>> AND a.IdPatientDate = t.IdPatientDate
> >>>> AND CONCAT(a.IdAppt, '0') = c.IdApptType
> >>>> AND a.IdPriCarePhy = af.IdAffil)
> >>>> WHERE a.ApptDate>= '2009-03-01';
> >>>>
> >>>> p.IdPatient, t.IdPatientDate, c.IdApptType, and af.IdAffil are all
> indexed. Also I selectively took out join parameters until there was
> nothing but a join on the patient table, and it was still slow, but when
> I took that out, the query was extremely fast. What might I be doing wrong?
> >>>>
> >>>> Thanks,
> >>>>
> >>>> Jim McNeely
> >>> The performance problem is with your Cartesian product. I think you
> meant to write:
> >>>
> >>> from Appt_ a
> >>> LEFT JOIN patient_ p
> >>> ON a.IdPatient = p.IdPatient
> >>> LEFT JOIN today_ t
> >>> ON a.IdPatientDate = t.IdPatientDate
> >>> LEFT JOIN Copy_ c
> >>> ON CONCAT(a.IdAppt, '0') = c.IdApptType
> >>> LEFT JOIN Affil_ af
> >>> ON a.IdPriCarePhy = af.IdAffil
> >>>
> >>> As of 5.0.12, the comma operator for table joins was demoted in the
> 'order of precedence' for query execution. That means that MySQL became
> more complaint with the SQL standard but it also means that using a
> comma-join instead of an explicit ANSI join can result in a Cartesian
> product more frequently.
> >>>
> >>> Try my style and compare how it works. If both styles are similarly
> slow, collect the EXPLAIN plan for this query and share with the list.
> >>>
> >>> Yours,
> >>> --
> >>> Shawn Green
> >>> MySQL Principal Technical Support Engineer
> >>> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> >>> Office: Blountville, TN
> >>>
> >>> --
> >>> MySQL General Mailing List
> >>> For list archives: http://lists.mysql.com/mysql
> >>> To
> unsubscribe: http://lists.mysql.com/mysql?unsub=jim@newcenturydata.com
> >>>
> >>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Help with slow query

On 3/10/2011 12:32, Jim McNeely wrote:
> Rhino,
>
> Thanks for the help and time! Actually, I thought the same thing, but what's weird is that is the only thing that doesn't slow it down. If I take out all of the join clauses EXCEPT that one the query runs virtually instantaneously. for some reason it will use the index in that case and it works. If I take out everything like this:
>
> SELECT a.IdAppt, a.IdPatient,
> p.NameLast, p.NameFirst, p.NameMI
>
> from Appt_ a
> LEFT JOIN patient_ p
> ON a.IdPatient = p.IdPatient
> WHERE a.ApptDate>= '2009-03-01';
>
> It is still utterly slow. EXPLAIN looks like this:
>
> +----+-------------+-------+-------+---------------+-------- --+---------+------+--------+-------------+
> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
> +----+-------------+-------+-------+---------------+-------- --+---------+------+--------+-------------+
> | 1 | SIMPLE | a | range | apptdate | apptdate | 4 | NULL | 296166 | Using where |
> | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 262465 | |
> +----+-------------+-------+-------+---------------+-------- --+---------+------+--------+-------------+
>
> But, very good try. I thought this might be it as well.
>
.... snip ...

According to this report, there are no indexes on the `patient_` table
that include the column `IdPatient` as the first column. Fix that and
this query should be much faster.

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Help with slow query

Shawn,

This is the first thing that I though as well, but here is a portion =
from the show create table for patient_:


PRIMARY KEY (`zzk`),
KEY `IdPatient` (`IdPatient`),
KEY `SSN` (`SSN`),
KEY `IdLastword` (`IdLastword`),
KEY `DOB` (`DateOfBirth`),
KEY `NameFirst` (`NameFirst`),
KEY `NameLast` (`NameLast`)

This extremely simple join is still massively slow.

Jim

On Mar 10, 2011, at 10:00 AM, Shawn Green (MySQL) wrote:

> On 3/10/2011 12:32, Jim McNeely wrote:
>> Rhino,
>>=20
>> Thanks for the help and time! Actually, I thought the same thing, but =
what's weird is that is the only thing that doesn't slow it down. If I =
take out all of the join clauses EXCEPT that one the query runs =
virtually instantaneously. for some reason it will use the index in that =
case and it works. If I take out everything like this:
>>=20
>> SELECT a.IdAppt, a.IdPatient,
>> p.NameLast, p.NameFirst, p.NameMI
>>=20
>> from Appt_ a
>> LEFT JOIN patient_ p
>> ON a.IdPatient =3D p.IdPatient
>> WHERE a.ApptDate>=3D '2009-03-01';
>>=20
>> It is still utterly slow. EXPLAIN looks like this:
>>=20
>> =
+----+-------------+-------+-------+---------------+-------- --+---------+-=
-----+--------+-------------+
>> | id | select_type | table | type | possible_keys | key | =
key_len | ref | rows | Extra |
>> =
+----+-------------+-------+-------+---------------+-------- --+---------+-=
-----+--------+-------------+
>> | 1 | SIMPLE | a | range | apptdate | apptdate | 4 =
| NULL | 296166 | Using where |
>> | 1 | SIMPLE | p | ALL | NULL | NULL | NULL =
| NULL | 262465 | |
>> =
+----+-------------+-------+-------+---------------+-------- --+---------+-=
-----+--------+-------------+
>>=20
>> But, very good try. I thought this might be it as well.
>>=20
> ... snip ...
>=20
> According to this report, there are no indexes on the `patient_` table =
that include the column `IdPatient` as the first column. Fix that and =
this query should be much faster.
>=20
> --=20
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Djim@newcenturydata.com
>=20


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Help with slow query

On 3/10/2011 13:12, Jim McNeely wrote:
> Shawn,
>
> This is the first thing that I though as well, but here is a portion from the show create table for patient_:
>
>
> PRIMARY KEY (`zzk`),
> KEY `IdPatient` (`IdPatient`),
> KEY `SSN` (`SSN`),
> KEY `IdLastword` (`IdLastword`),
> KEY `DOB` (`DateOfBirth`),
> KEY `NameFirst` (`NameFirst`),
> KEY `NameLast` (`NameLast`)
>
> This extremely simple join is still massively slow.
>
> Jim
>
> On Mar 10, 2011, at 10:00 AM, Shawn Green (MySQL) wrote:
>
>> On 3/10/2011 12:32, Jim McNeely wrote:
>>> Rhino,
>>>
>>> Thanks for the help and time! Actually, I thought the same thing, but what's weird is that is the only thing that doesn't slow it down. If I take out all of the join clauses EXCEPT that one the query runs virtually instantaneously. for some reason it will use the index in that case and it works. If I take out everything like this:
>>>
>>> SELECT a.IdAppt, a.IdPatient,
>>> p.NameLast, p.NameFirst, p.NameMI
>>>
>>> from Appt_ a
>>> LEFT JOIN patient_ p
>>> ON a.IdPatient = p.IdPatient
>>> WHERE a.ApptDate>= '2009-03-01';
>>>

1) Verify that the indexes on `patient_` haven't been disabled

SHOW INDEXES FROM `patient_`;

http://dev.mysql.com/doc/refman/5.5/en/show-index.html

2) Verify that the data types of `Appt_`.`IdPatient` and
`patient_`.`IdPatient` are not incompatible. (for example: one is
varchar, the other int)

Thanks,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Help with slow query

On 3/10/11 10:46 AM, Shawn Green (MySQL) wrote:
>>> On 3/10/2011 12:32, Jim McNeely wrote:
>>>> Rhino,
>>>>
>>>> Thanks for the help and time! Actually, I thought the same thing, but what's weird is that is the only thing that doesn't slow it down.
>>>> If I take out all of the join clauses EXCEPT that one the query runs virtually instantaneously. for some reason it will use the index in
>>>> that case and it works. If I take out everything like this:
>>>>
>>>> SELECT a.IdAppt, a.IdPatient,
>>>> p.NameLast, p.NameFirst, p.NameMI
>>>>
>>>> from Appt_ a
>>>> LEFT JOIN patient_ p
>>>> ON a.IdPatient = p.IdPatient
>>>> WHERE a.ApptDate>= '2009-03-01';
>>>>
>
> 1) Verify that the indexes on `patient_` haven't been disabled
>
> SHOW INDEXES FROM `patient_`;
>
> http://dev.mysql.com/doc/refman/5.5/en/show-index.html
>
> 2) Verify that the data types of `Appt_`.`IdPatient` and `patient_`.`IdPatient` are not incompatible. (for example: one is varchar, the
> other int)

This last one can be HUGE. I tracked a big performance issue to this exact
problem - the columns used in the join had the same name, but different
data types. Correcting to be the same type (both ints) made a terrific
performance increase.



--
Andy Wallace
iHOUSEweb, Inc.
awallace@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org