LEFT JOIN limitations ??

[Marek Lewczuk]

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/my***********...ie.nctu.edu.tw