MySQL Forums
Forum List  »  Full-Text Search

Fulltext doing temp table
Posted by: David Burleson
Date: October 01, 2007 06:53AM

I have 2 queries on my site that are causing major load problems and locking others our of the table. Each ends up doing a temp table. Anyone know where i can improve? I wouldn't think a fulltext on a forum would be so unusual.

DROP TABLE IF EXISTS `ezinev4`.`forum_questions`;
CREATE TABLE `ezinev4`.`forum_questions` (
`forum_questions_id` mediumint(8) unsigned NOT NULL auto_increment,
`forum_categories_id` smallint(5) unsigned NOT NULL,
`user_id` mediumint(8) unsigned default NULL,
`last_updated_user_id` mediumint(8) unsigned default '0',
`last_updated_date` datetime default NULL,
`added_date` datetime default NULL,
`question_status` tinyint(3) unsigned default '1',
`restrict_robot` tinyint(3) unsigned default '0',
`locked_status` tinyint(3) unsigned default '0',
`locked_reason` varchar(255) default NULL,
`title` varchar(255) default NULL,
`username` varchar(50) default NULL,
`replies_count` smallint(5) unsigned default '0',
`show_in_recent` tinyint(3) unsigned default '1',
`type` tinyint(3) unsigned default '0' COMMENT '0 = normal, 1 = sticky, 2 = announcement',
PRIMARY KEY (`forum_questions_id`),
KEY `Index_user_id` (`user_id`),
KEY `Index_question_status` (`question_status`),
KEY `Index_category_id` (`forum_categories_id`),
FULLTEXT KEY `ft_test` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=52982 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

DROP TABLE IF EXISTS `ezinev4`.`comments_forum`;
CREATE TABLE `ezinev4`.`comments_forum` (
`forum_comments_id` int(10) unsigned NOT NULL auto_increment,
`old_comments_id` mediumint(8) unsigned default NULL,
`user_id` mediumint(8) unsigned default NULL,
`content` text character set latin1,
`comments_status` tinyint(3) unsigned default NULL,
`added_date` datetime default NULL,
`category_id` tinyint(3) unsigned default NULL,
`helpful` tinyint(3) unsigned default NULL,
`modified_date` datetime default NULL,
`forum_questions_id` mediumint(8) unsigned default NULL,
`username` varchar(50) character set latin1 default NULL,
`last_mod_user_id` mediumint(8) unsigned default NULL,
PRIMARY KEY (`forum_comments_id`),
KEY `Index_question_id` (`forum_questions_id`),
KEY `Index_user_id` (`user_id`),
KEY `Index_comments_status` (`comments_status`),
FULLTEXT KEY `full_text_content` (`content`)
) ENGINE=MyISAM AUTO_INCREMENT=1456776 DEFAULT CHARSET=utf8;

SELECT comments_forum.forum_questions_id,comments_forum.content,date_format(comments_forum.added_date,'%e %b %Y - %H:%i') as formatted_date, MATCH (comments_forum.content) AGAINST ('words') AS RELEVANCE FROM comments_forum FORCE INDEX (full_text_content) INNER JOIN forum_questions ON forum_questions.forum_questions_id = comments_forum.forum_questions_id WHERE MATCH (comments_forum.content) AGAINST ('+words' IN BOOLEAN MODE) AND comments_forum.comments_status = 1 AND forum_questions.forum_categories_id!=237 GROUP BY forum_questions_id ORDER BY RELEVANCE DESC

SELECT DISTINCT forum_questions_id FROM comments_forum force index(Index_user_id) WHERE user_id=52194 ORDER BY forum_comments_id DESC LIMIT 15

Options: ReplyQuote


Subject
Views
Written By
Posted
Fulltext doing temp table
5424
October 01, 2007 06:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.