FULLTEXT 검색

OS: Microsoft Windows 2000 5.00.2195 Service Pack 3
MySQL: 4.0.7-gamma-nt
CPU: x86 Family 6 Model 8 Stepping 10
RAM: 512MB
작성자: 강명규(kang@dbakorea.pe.kr)


FULLTEXT search

fulltext검색은 쉬운 말로 자연어 검색(natural language search)이다.

FULLTEXT 인덱스는 MyISAM table type을 가진 테이블에서만 지원되고,
CHAR, VARCHAR, TEXT column type을 가진 컬럼에서만 생성될 수 있다.
char는 언급되어 있으나, 직접 확인하진 못했으므로 함 해보기 바란다.

Feature                     Version
Basic FULLTEXT searching    3.23.23
Configurable parameters     4.0.0
Boolean searches            4.0.1
Phrase searches             4.0.2

Server variables
길이가 ft_min_word_len ~ ft_max_word_len 의 범위에 있는 것들만 검색 대상이 됨.
ft_min_word_len : (디폴트:4)
ft_max_word_len : (디폴트:254)


서버변수 변경
1. C:\WINNT\my.ini변경후 서버 restart(UNIX: /etc/my.cnf)
[mysqld]
set-variable = ft_min_word_len=5

2. 기존 fulltext인덱스 변경
REPAIR TABLE 테이블명 USE_FRM;


FULLTEXT 인덱스는 테이블 생성후, 대량의 데이터를 로드한 다음에 생성해주는 것이 좋다.
이 방식은 fulltext인덱스만이 아닌 다른 인덱스에도 동일하게 적용된다.

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
);

INSERT INTO articles VALUES
(0,'MySQL Tutorial', 'DBMS stands for DataBase ...'),
(0,'How To Use MySQL Efficiently', 'After you went through a ...'),
(0,'Optimising MySQL','In this tutorial we will show ...'),
(0,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
(0,'MySQL vs. YourSQL', 'In the following database comparison ...'),
(0,'MySQL Security', 'When configured properly, MySQL ...');


mysql> select * from articles;
+----+------------------------------+------------------------------------------+
| id | title                                         | body                                                       |
+----+------------------------------+------------------------------------------+
|  1 | MySQL Tutorial                        | DBMS stands for DataBase ...                    |
|  2 | How To Use MySQL Efficiently  | After you went through a ...                         |
|  3 | Optimising MySQL                   | In this tutorial we will show ...                     |
|  4 | 1001 MySQL Tricks                  | 1. Never run mysqld as root. 2. ...                |
|  5 | MySQL vs. YourSQL               | In the following database comparison ...       |
|  6 | MySQL Security                      | When configured properly, MySQL ...           |
+----+------------------------------+------------------------------------------+
6 rows in set (0.00 sec)


match에는 검색될 컬럼(들)을 적고, against에는 검색할 단어를 적는다.
match에 컬럼순서는 상관없고, against는 대소문자 관계없이 검색된다.

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title             |            body                                                        |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ...    |
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...                   |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)


검색된 결과는 Relevance values 라는 값에 의해 정렬되는데 이것은 검색결과의 정확도를 의미한다고 보면 되겠다.
0의 값은 아무런 관련이 없다는 뜻이고, 계산기준은 다음과 같다.
row내에 단어의 수, 해당 row내의 unique한 단어수, collection내의 총단어수, 특정 단어를 포함하는 문서(rows)의 수.
번역하긴 했는데 정확히 무슨 말이라는 건지.. --;

위의 결과에 대해 Relevance values를 알아보면 다음과 같다.
id=5가 가장 큰 값이므로, 위에서 제일 처음에 표시 되었음을 알 수 있다. 0은 관련없는 것들이므로 검색결과에서
제외되었음도 봐두자.

mysql> SELECT id, MATCH (title,body) AGAINST ('database') from articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('database')   |
+----+-----------------------------------------+
|  1 |                                       0.65545834044456 |
|  2 |                                                            0 |
|  3 |                                                            0 |
|  4 |                                                            0 |
|  5 |                                      0.66266459031789 |
|  6 |                                                            0 |
+----+-----------------------------------------+
6 rows in set (0.00 sec)


검색에 사용되는 단어는 문자,숫자,', _ 이다.
검색에서 제외되는 단어(stopword)는 너무 짧은 단어(디폴트로 3단어이하, 서버변수 ft_min_word_len에서 지정)
이거나, stopword list에 포함된 단어들(the,..)이다.
자주 사용되는 단어나 문법적인 관계에 쓰이는 단어는 검색시 비중이 낮고,
드물게 사용되는 단어(용어, 학술어, ...)는 높은 비중을 가진다. 이 비중(weight)은 Relevance계산시 이용된다.


mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)

분명히 mysql이라는 단어가 존재함에도 불구하고, 위의 질의는 아무런 결과를 리턴하지 않는다.
이유는 MySQL이라는 단어가 너무 많이 존재하기 때문이다. 검색어가 rows에 반이 넘게 존재하면,
stopword로 취급된다.(50% threshold)


4.0.1이상부터는 in boolean mode 를 사용하면 50% threshold가 적용되지 않으며 다음과 같이 검색결과를 얻을 수 있다.
boolean mode에서는 FULLTEXT인덱스가 걸린 컬럼이 아니더라도 검색할 수 있다. 하지만 느려진다.
또한, 검색결과는 Relevance values에 의한 정렬이 이루어지지 않는다.

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL' IN BOOLEAN MODE);
+----+------------------------------+------------------------------------------+
| id | title                                         | body                                                       |
+----+------------------------------+------------------------------------------+
|  1 | MySQL Tutorial                        | DBMS stands for DataBase ...                    |
|  2 | How To Use MySQL Efficiently   | After you went through a ...                        |
|  3 | Optimising MySQL                    | In this tutorial we will show ...                    |
|  4 | 1001 MySQL Tricks                   | 1. Never run mysqld as root. 2. ...               |
|  5 | MySQL vs. YourSQL                 | In the following database comparison ...     |
|  6 | MySQL Security                       | When configured properly, MySQL ...          |
+----+------------------------------+------------------------------------------+
6 rows in set (0.00 sec)


boolean mode에서는, 검색에서 제외할 단어는 -로, 검색에 포함될 단어는 +로 지정할 수도 있다.
mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+mysql -tutorial' in boolean mode);

+----+------------------------------+------------------------------------------+
| id | title                                         | body                                                       |
+----+------------------------------+------------------------------------------+
|  2 | How To Use MySQL Efficiently   | After you went through a ...                       |
|  4 | 1001 MySQL Tricks                   | 1. Never run mysqld as root. 2. ...              |
|  5 | MySQL vs. YourSQL                 | In the following database comparison ...    |
|  6 | MySQL Security                       | When configured properly, MySQL ...         |
+----+------------------------------+------------------------------------------+
4 rows in set (0.00 sec)


+,- 와 더불어 다음의 operator도 사용할 수 있다.
+ : 검색시 반드시 포함될 단어
- : 검색시 제외될 단어
< : 단어의 검색능력에 대한 기여도(contribution)를 감소시킴
> : 단어의 검색능력에 대한 기여도(contribution)를 감소시킴
(): 검색 단어들을 subexpressions으로 그룹화시킴
~ : negative 오퍼레이터로 작용. 검색능력에 대한 기여도를 음수로 만듬.
    -와 유사하지만 제외되지는 않음. noise word를 의미할때 사용될 수 있다.
* : 일반적인 유닉스 쉘상의 기능과 동일(truncation operator)
" : ""로 쌓인 순서대로 있는 것만 검색됨.

예)
apple banana
find rows that contain at least one of these words.

+apple +juice
... both words.

+apple macintosh
... word ``apple'', but rank it higher if it also contain ``macintosh''.

+apple -macintosh
... word ``apple'' but not ``macintosh''.

+apple +(>pie <strudel)
... ``apple'' and ``pie'', or ``apple'' and ``strudel'' (in any order), but rank ``apple pie'' higher than ``apple strudel''.

apple*
... ``apple'', ``apples'', ``applesauce'', and ``applet''.

"some words"
... ``some words of wisdom'', but not ``some noise words''.


개인적인 견해
multi-byte문자인 한글을 사용하는 우리나라에서는 이 기능이 필요없다.
자연어검색이 안된다. MS-SQL에서도 한글의 자연어검색은 안되는 것으로 알고 있는데 지금도 그러려나..
암튼 영어가 아니라면, 사용하지 않는 것이 좋을 것이다. 당연히 성능도 떨어지는 것은 자명한 것이고..
stopword라는 검색시 제외되는 단어는 현재 소스의 변경을 통해서만 가능했다.
별도의 list file을 유지하는 것이 어떨까 싶네..