CODEWITHSUNDEEP
mysqlindexingfull-text-search
Ivan
Ivan

Reputation: 15922

Should I use FULLTEXT index for varchar?

If I have a structure like this:

CREATE TABLE IF NOT EXISTS `sheet` (
  `id` int(11) NOT NULL,
  `title` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `html` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `title` (`title`),
  FULLTEXT KEY `html` (`html`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

and I need to do full text search queries like this:

SELECT * FROM `cms`
WHERE MATCH ( title, html) AGAINST ('+psoriasis +categorizacion' IN BOOLEAN MODE)

Should I add a FULLTEXT index for title?

Upvotes: 0

Views: 2085

Answers (1)

RandomSeed
RandomSeed

Reputation: 29769

In fact you should modify/replace your existing index so that it covers both columns (if you use WHERE MATCH(title, html)..., as opposed to `WHERE MATCH(title)... AND MATCH(html)...):

CREATE TABLE sheet (
    ...
    FULLTEXT KEY title_html (title, html)
    ...
)

Upvotes: 2

Related Questions