Hello, I have a strange problem, maybe some of you will be able to explain me something. I use LEFT JOIN as a substitute for subselects. It's true that many subselects can be rewriten using LEFT JOIN. I have made a query which use LEFT JOIN statement and... when there are many LEFT JOIN's (over 3) on the same table MySQL execute this query very long time... few hours or more. Maybe there is something wrong with my table structures... Are there any limitations for LEFT JOIN ??
Below the query, and table structures... -- QUERY SELECT DISTINCT _NEW.news_id, _NNA.title, IF(LENGTH(_NNA.content) > 250, CONCAT(SUBSTRING(_NNA.content, 1, 250),'...'), _NNA.content) AS content, _NEW.publish_date FROM new_news _NEW, new_news_names_search _NNA LEFT JOIN new_news_categorys _CAT21 ON _CAT21.news_id = _NEW.news_id AND _CAT21.category_id IN (21) LEFT JOIN new_news_categorys _CAT18 ON _CAT18.news_id = _NEW.news_id AND _CAT18.category_id IN (18) LEFT JOIN new_news_categorys _CAT50 ON _CAT50.news_id = _NEW.news_id AND _CAT50.category_id IN (50) LEFT JOIN new_news_categorys _CAT1 ON _CAT1.news_id = _NEW.news_id AND _CAT1.category_id IN (1,2,3,17,19,30,37) WHERE _NEW.publish_date <= NOW() AND _NNA.news_id = _NEW.news_id AND _NNA.language_id = '1' AND _CAT21.news_id > 0 AND _CAT18.news_id > 0 AND _CAT50.news_id > 0 AND _CAT1.news_id > 0 ORDER BY _NEW.publish_date DESC # # Table structure for table 'new_news_categorys' # CREATE TABLE new_news_categorys ( news_id int(10) NOT NULL default '0', category_id int(10) NOT NULL default '0', PRIMARY KEY (news_id,category_id), KEY category_id (category_id), CONSTRAINT 0_1901 FOREIGN KEY (news_id) REFERENCES new_news (news_id) ON DELETE CASCADE, CONSTRAINT 0_1902 FOREIGN KEY (category_id) REFERENCES set_new_news_categorys (category_id) ON DELETE CASCADE ) TYPE=InnoDB; # # Table structure for table 'new_news_names' # CREATE TABLE new_news_names ( news_id int(10) NOT NULL default '0', language_id int(10) NOT NULL default '0', title longtext, content longtext, PRIMARY KEY (news_id,language_id), KEY news_id (news_id), KEY language_id (language_id), CONSTRAINT 0_1909 FOREIGN KEY (news_id) REFERENCES new_news (news_id) ON DELETE CASCADE ) TYPE=InnoDB; # # Table structure for table 'new_news_names_search' # CREATE TABLE new_news_names_search ( news_id int(11) NOT NULL default '0', language_id int(11) NOT NULL default '0', title longtext, content longtext, PRIMARY KEY (news_id,language_id), FULLTEXT KEY title (title,content) ) TYPE=MyISAM; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
