List:General Discussion« Previous MessageNext Message »
From:mos Date:March 10 2011 5:46pm
Subject:Re: Help with slow query
View as plain text  
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=1
> >>>
> >>

Thread
Help with slow queryJim McNeely10 Mar
  • Re: Help with slow queryMySQL)10 Mar
    • Re: Help with slow queryJim McNeely10 Mar
Re: Help with slow queryJim McNeely10 Mar
  • Re: Help with slow querymos10 Mar
  • Re: Help with slow queryMySQL)10 Mar
    • Re: Help with slow queryJim McNeely10 Mar
      • Re: Help with slow queryMySQL)10 Mar
        • Re: Help with slow queryAndy Wallace10 Mar