Fulltext doing temp table
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