Re: Re: Left Join extremely slow (MySQL)

"explain" - - - - - - - - - - Scott Poarch www.globalhost.com - - - - - - - - - - > From: "Richard Lynch" <ceo@l-i-e.com> > Organization: Lynch Interplanetary Enterprises > Date: Fri, 3 Aug 2001 17:09:49 -0500 > To: <php-general@lists.php.net> > Subject: [PHP] Re: Left Join extremely slow (MySQL) > > Use MySQL's built in "analyze" ("describe"? SQL statement to see > what's > taking so long). > > I'm betting on one server having an INDEX on the id or ex fields, and the > other not. > > -- > WARNING richard@zend.com address is an endangered species -- Use > ceo@l-i-e.com > Wanna help me out? Like Music? Buy a CD: >http://l-i-e.com/artists.htm > Volunteer a little time: http://chatmusic.com/volunteer.htm > ----- Original Message ----- > From: James Crowley <james@vbweb.co.uk> > Newsgroups: php.general > To: <php-general@lists.php.net> > Sent: Friday, August 03, 2001 11:40 AM > Subject: Left Join extremely slow (MySQL) > > >> Hi, >> I had been using an INNER JOIN statement for a MySQL query, which >> worked fine. However, my new ISP does not have the latest version of >> MySQL. The version they have does not support conditions for Inner Join >> statements, so I have tried switching to a Left Join instead. However, >> the Left Join query takes 12.15 sec (which is obviously far too slow). >> The Inner join statement, meanwhile, completed the query in 0.04. Any >> idea why?? >> >> Here are the two statements (fast): >> >> SELECT i.id, i.title, i.description FROM dir_items i INNER JOIN >> dir_assoc a ON a.itemid=i.id WHERE a.parentid=59 AND a.ex=0 >> >> Here are the two statements (slow): >> >> SELECT i.id, i.title, i.description FROM dir_items i LEFT JOIN dir_assoc >> a ON a.itemid=i.id WHERE a.parentid=59 AND a.ex=0 >> >> Regards, >> >> - James >> >> Editor, VB Web >> ================== >> Web - http://www.vbweb.co.uk >> Email - james@vbweb.co.uk >> ICQ# - 60612011 >> Fax - +44(0)8707052859 >> ================== >> > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net > For additional commands, e-mail: php-general-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net > >