2

I'm trying to understand FULLTEXT indexes.

Is there some SQL query to test 'ft_min_word_len' variable?

I've tried to match four characters only in the example below but it doesn't work. Why the third row is returned if there is no match for '(database|data)'

mysql> SHOW VARIABLES LIKE 'ft_min_word_len';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| ft_min_word_len | 4     |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE articles\G
*************************** 1. row ***************************
       Table: articles
Create Table: CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `body` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title` (`title`,`body`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' WITH QUERY EXPANSION);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  3 | Optimizing MySQL  | In this tutorial we will show ...        |
+----+-------------------+------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('data' WITH QUERY EXPANSION);
Empty set (0.00 sec)
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
HTF
  • 459
  • 4
  • 13
  • 27

2 Answers2

2

To retrieve ft_min_word_len as a query, run this:

SELECT variable_value FROM information_schema.global_variables
WHERE variable_name='ft_min_word_len';

But wait, you are asking

Why the third row is returned if there is no match for '(database|data)'

Did you know there are 543 words that are excluded from FULLTEXT indexes ?

Note the following from your query and row data...

  • The words will and this are in the stopword list.
  • The word show is not in the stopword list, so searching for show should produce a tangible result.
  • The word data is not in the body column as its own word. Try searching for data* or +data*

I wrote about this before and how to disable the default stopword list:

UPDATE 2013-04-25 12:26 EDT

@Michael-sqlbot has pointed out in his comment to my answer that QUERY EXPANSION does a double lookup to include "near-misses" due to relevance. If @Michael-sqlbot adds his comment as an answer, I will retract my answer, upvote his answer, and ask that @Michael-sqlbot's answer be accepted.

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • Thanks for your reply. I was actually looking for a query to test this variable. Basically I would like to prove that I can search with 3 characters if 'ft_min_word_len' is set to '3'. I don't have access to application hence this approach. – HTF Apr 24 '13 at 18:54
  • 1
    But the stopword list has 3 letter words in it, including 1) all, 2) can, 3) far, 4) for, 5) old. – RolandoMySQLDBA Apr 24 '13 at 18:56
  • 2
    The third row is returned in the example because the query uses WITH QUERY EXPANSION which "works by performing the search twice, where the search phrase for the second search is the original search phrase concatenated with the few most highly relevant documents from the first search" http://dev.mysql.com/doc/refman/5.5/en/fulltext-query-expansion.html... nothing to do with stopwords. – Michael - sqlbot Apr 25 '13 at 02:26
  • @Michael-sqlbot I can retract my answer if you submit your comment as an answer. – RolandoMySQLDBA Apr 25 '13 at 02:35
2

Since ft_min_word_len is set to 4, MySQL won't index words shorter than 4 letters in FULLTEXT indexes.

Furthermore, as Rolando mentioned in a separate answer, MySQL also will not index stopwords. You can override the default stopword list if you want, but by default many four letter words are not indexed,

To prove that this is working properly, you just need to pick a four letter word from your table that is not a stopword, and search for that. Looking at your brief sample, I see 2 four letter words that are not default stopwords:

  • DBMS
  • show

You can test those with the following queries:

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('DBMS');
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('show');

To illustrate the point that words shorter than 4 characters are not indexed, pick one of those words that is not a default stopword, query for it, and you should get no results. The only candidate I see in your data is "vs", so use that:

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('vs');
Ike Walker
  • 754
  • 1
  • 6
  • 15