I'm in the process of moving to MariaDB on RDS. My tables are up and running and everything looks good, but given that I'm on MariaDB 10.0.24, I'm considering switching a couple of my MyISAM tables over to Innodb. Prior to this, I required using MyISAM because InnoDb had no support for full-text. But, as I understand it, MariaDB > 10.0.5 supports full-text searching on InnoDB.
In my first attempt, I converted the table from MyISAM to InnoDB using:
ALTER TABLE `courses` ENGINE=INNODB
But after that, my FULLTEXT indexes appeared to have been reset. They're still listed there, but don't respond to FULLTEXT searching.
Can't find FULLTEXT index matching the column list
Also, when I attempt to create new FULLTEXT INDEXES on the table, I can no longer create them -- only INDEX and UNIQUE are available.
Finally, while I see settings in my RDS parameter group that let me configure the INNODB FULLTEXT settings, I didn't see any setting that would let me "turn it on", so I'm assuming it's on by default.
Am I stuck with MyISAM or is it possible to change these tables to InnoDB and I just haven't figured out how to do that?
Thanks for your help!
Update:
SHOW CREATE TABLE:
CREATE TABLE `courses` (
`id` varchar(32) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`description` text,
`instructor_id` varchar(32) DEFAULT NULL,
`catalog_id` varchar(32) NOT NULL,
`site_id` varchar(32) NOT NULL,
`created_on` datetime NOT NULL,
`modified_on` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `site_id` (`site_id`),
KEY `instructor_id` (`instructor_id`),
KEY `catalog_id` (`catalog_id`),
FULLTEXT KEY `name_description` (`name`,`description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query:
SELECT `c`.*, (MATCH (c.name) AGAINST ('+yoga*' IN BOOLEAN MODE)) AS `nameRelevance`, (MATCH (c.description) AGAINST ('+yoga*' IN BOOLEAN MODE)) AS `descriptionRelevance` FROM `courses` AS `c` WHERE (c.catalog_id IN ('xxxxx')) AND (c.site_id = 'yyyyy') AND (MATCH (c.name, c.description) AGAINST ('+yoga*' IN BOOLEAN MODE) OR MATCH (c.name, c.description) AGAINST ('+yoga*' IN BOOLEAN MODE))
Error: Can't find FULLTEXT index matching the column list
OK, I figured it out. It looks as though in MyISAM tables, my query against name and description fields works even without a separate FULLTEXT key for name or description. Ie, in my definition above, you can see that I have added one FULLTEXT INDEX for the combination of
name_description
, but in my query I'm targeting those columns individually as well. In MyISAM, this works. In InnoDb, it does not.The solution was to create individual FULLTEXT INDEXes for name and description alongside the combo index. Now my query works great!