MATCH AGAINST one character words returns empty rows with ft_min_word_len = 1

Posted on

Question :

I’ve set ft_min_word_len = 1 to my.cnf file, also SHOW VARIABLES LIKE 'ft_min_word%' displays ft_min_word_len as 1. Then I’ve reloaded all database tables and it still doesn’t search for one character words.

My table:

CREATE TABLE IF NOT EXISTS event (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
description TEXT,
time TIMESTAMP DEFAULT 0,
FULLTEXT(name)
);

My query:

SELECT event.id AS eid,
    event.name AS ename
FROM event
WHERE MATCH(event.name) AGAINST('+W*' IN BOOLEAN MODE)

And event name I’m searching for is W.W.W. + Pavel Fajt + Vladimir 518 @ Sono

What else could cause the problem please?

EDIT: I’m using InnoDB, sorry I haven’t mention it before.

Answer :

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

mysql> SELECT event.id AS eid,     
              event.name AS ename 
       FROM event 
       WHERE MATCH(event.name) AGAINST('+W*' IN BOOLEAN MODE);
+-----+--------+
| eid | ename  |
+-----+--------+
|   1 | W.W.W. |
|   7 | W      |
|  12 | Wa     |
|  11 | Wat    |
|  10 | Wats   |
|   9 | Watso  |
|   8 | Watson |
+-----+--------+
7 rows in set (0.01 sec)

Maybe you are using InnoDB:

mysql> ALTER TABLE event ENGINE=InnoDB;
Query OK, 12 rows affected (0.51 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> SELECT event.id AS eid,
       event.name AS ename 
       FROM event 
       WHERE MATCH(event.name) AGAINST('+W*' IN BOOLEAN MODE);
+-----+--------+
| eid | ename  |
+-----+--------+
|   8 | Watson |
|   9 | Watso  |
|  10 | Wats   |
|  11 | Wat    |
+-----+--------+
4 rows in set (0.00 sec)

Where the option to change is innodb_ft_min_token_size (ft_min_word_len is for MyISAM only).

Leave a Reply

Your email address will not be published. Required fields are marked *