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