CODEWITHSUNDEEP
mysqlsqlindexingfull-text-indexing
Ned Hulton
Ned Hulton

Reputation: 678

MySQL Match Against: easiest way to deal with stopwords

I get zero results from this query:

SELECT COUNT(1) FROM `myTable` WHERE MATCH(tagline) AGAINST(' +IT professional' IN BOOLEAN MODE)

I get 92 from this:

SELECT COUNT(1) FROM `myTable` WHERE `tagline` LIKE '%IT professional%'

I prefer the first query because MATCH against an index is 80% faster with my setup. Obviously "IT" is a stopword and causing problems. What is the easiest and most expedient way of dealing with this problem if I want the functionality of the second query and the speed of the first one?

Upvotes: 2

Views: 451

Answers (1)

Rick James
Rick James

Reputation: 142433

If innodb_ft_min_token_size is the default of 3, then IT is "too short" (as opposed to "a stopword"). Further note that +IT insists on IT, but, since IT is totally ignored, it is never found. Hence the "zero results".

I have had good success with this approach to arbitrary data coming from the user: If the word is 3 or more characters long, prefix it with +, else leave it alone:

... AGAINST('it +professional' IN BOOLEAN MODE)

That has the effect of ignoring "it" and finding all rows with "professional", "professionals", etc.

In some situations (perhaps not yours) this can be useful:

WHERE MATCH(fullname) AGAINST("+Rick +James" IN BOOLEAN MODE)
  AND fullname LIKE "%Rick James%"

That works fast (because the fulltext is quite efficient and the LIKE is performed only against those that match the FT test).

And it avoids

... Rick Smith and James Davis ...

In your case, this might be appropriate:

WHERE MATCH(tagline) AGAINST('IT +professional' IN BOOLEAN MODE)
  AND tagline LIKE '%IT +professional%'

The FT test finds all taglines with "professional" whether "IT" or not; the LIKE further filters.

Your "80% faster" says to me that the table is not very big. The improvement for my tips grows with table size.

Upvotes: 2

Related Questions