0

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

Matt James
  • 113
  • 5
  • Seems to work okay in MariaDB 10.0.17 for RDS, haven't tested in 10.0.24... post your current `SHOW CREATE TABLE` and an example of a query that is throwing this error? Also `only INDEX and UNIQUE are available` -- where do you see this? Is it an error message, or...? – Michael - sqlbot May 24 '16 at 20:36
  • @Michael-sqlbot Great questions - I updated my original question to address this. As for the "only INDEX and UNIQUE are available": I should have been more clear about that. When I look at creating a new index on this table using Sequel Pro, I only have those as index types in my dropdown. In the MyISAM version of the table, I see FULLTEXT in the list. Could be Sequel Pro specific, though. – Matt James May 25 '16 at 20:01

1 Answers1

0

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!

Matt James
  • 113
  • 5