在 innodb 上使用两个 JOIN 进行全文搜索

Question

我正在尝试在mysql 5.6上使用innodb进行全文搜索，并在submissions.title, submissions.description上添加了一个FULLTEXT KEY。这是我对submissions show create table：

Create Table: CREATE TABLE `submissions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` mediumtext NOT NULL,
  `user_id` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `type` enum('tip','request') NOT NULL,
  `thumbnail` varchar(64) CHARACTER SET latin1 DEFAULT NULL,
  `removed` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `keywords` varchar(255) NOT NULL,
  `ip` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  FULLTEXT KEY `title` (`title`,`description`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8

当我运行以下查询时：

SELECT s.*, MATCH (s.title, s.description) AGAINST ('whatever'), u.username, SUM( sv.up ) helpfulVotes
FROM submissions s
INNER JOIN users u ON s.user_id = u.id
LEFT JOIN submissions_votes sv ON s.id = sv.submission_id
GROUP BY s.id
ORDER BY s.created DESC 
LIMIT 0 , 30

我被#1214 - The used table type doesn't support FULLTEXT indexes为错误。

这是否意味着我需要向要加入的其他字段添加全文？真的没有意义，因为我没有搜索任何这些字段......

编辑：正在我的生产机器上进行查询，该机器具有较旧的mysql版本，而且我的查询是错误的。

正确查询：

 SELECT s . * , u.username, SUM( sv.up ) helpfulVotes
 FROM submissions s
 INNER JOIN users u ON s.user_id = u.id
 LEFT JOIN submissions_votes sv ON s.id = sv.submission_id
 WHERE MATCH (
 s.title, s.description
 )
 AGAINST (
 'whatever'
 )
 LIMIT 0 , 30;

Answer 1

我认为全文搜索只是ISAM。