patternsqlMinor
MATCH AGAINST one character words returns empty rows with ft_min_word_len = 1
Viewed 0 times
rowswithwordsmatchcharacteremptyagainstoneft_min_word_lenreturns
Problem
I've set
My table:
My query:
And event name I'm searching for is
What else could cause the problem please?
EDIT: I'm using InnoDB, sorry I haven't mention it before.
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 @ SonoWhat else could cause the problem please?
EDIT: I'm using InnoDB, sorry I haven't mention it before.
Solution
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).Code Snippets
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)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)Context
StackExchange Database Administrators Q#76653, answer score: 8
Revisions (0)
No revisions yet.