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