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=j...@newcenturydata.com
>>>
>>
