Re: Re: Left Join extremely slow (MySQL)

>"explain" >> >> Use MySQL's built in "analyze" ("describe"? SQL statement to see >> what's >> taking so long). The statement is called EXPLAIN. You can find it described here: http://www.mysql.com/doc/E/X/EXPLAIN.html >> I'm betting on one server having an INDEX on the id or ex fields, and the >> other not. If the following fields are missing indexed your query will be slower. itemid id parentid ex You can check the indexes on tables using SHOW INDEX FROM tablename. If the indexes are missing add them with ALTER TABLE ADD INDEX: http://www.mysql.com/doc/A/L/ALTER_TABLE.html >> >>> 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 >>> To do an INNER JOIN you simply do SELECT * FROM table_a, table_b WHERE table_a.id = table_b.id http://www.mysql.com/doc/J/O/JOIN.html Exchanging an inner (full) join for a left join might not have desired result. The MySQL documentation is very good. http://www.mysql.com/doc/index.html Tina. -- Tina Gunnarsson Phone: 408 732 1413 1616 Hollenbeck Ave. #20 Cell: 406 504 7310 Sunnyvale, CA 94087 "We only begin to live when we conceive life as Tragedy..." - W. B. Yeats