http://www.tuxradar.com/practicalphp/9/3/18 에서 참조

Advanced text searching using full-text indexes

Creating an index of large text fields is not practical in its default state – text fields have certain characteristics that warrant their own form of indexing. For example, many simple words (“at”, “the”, etc) are unimportant, and therefore aren’t stored in FULLTEXT indexes. Furthermore, MySQL has an infamous “50% rule”, whereby it will not bother indexing words that appear in the more than 50% of records simply because it will not be of use.

Creating a full text index is done after creating your table, using the following command: ALTER TABLE some_table ADD FULLTEXT (some_field). Here is an example:

ALTER TABLE usertable ADD FULLTEXT(FirstName);

Once we have a FULLTEXT index on our table, it opens up a whole new world of pattern matching, which, as of MySQL 4, allows us to do boolean matching with little fuss. FULLTEXT queries look a little more complicated that normal SELECT queries, but are much more powerful – they can even be used to return “match quality”, as you will soon see. To properly demonstrate FULLTEXT indexes, we need a table with lots of text in:

CREATE TABLE opinions (Opinion CHAR(100));
INSERT INTO opinions VALUES ('MySQL is a very fast database');
INSERT INTO opinions VALUES ('Green is everyone's favourite colour');
INSERT INTO opinions VALUES ('Databases are helpful for storing data');
INSERT INTO opinions VALUES ('PHP is a very nice language');
INSERT INTO opinions VALUES ('Spain is a nice country to visit');
INSERT INTO opinions VALUES ('Perl isn't as nice a language as PHP');
INSERT INTO opinions VALUES ('This is a blank row to avoid the 50% rule');
ALTER TABLE opinions ADD FULLTEXT (Opinion);

That gives us a very basic table with a FULLTEXT index on the opinion text. From there, we can jump in with a very basic SELECT query to match all rows with “nice” in:

SELECT * FROM opinions WHERE MATCH(Opinion) AGAINST ('nice');

That query generates the following output:

+---------------------------------------+
| Opinion                               |
+---------------------------------------+
| PHP is a very nice language           |
| Spain is a nice country to visit      |
| Perl is not as nice a language as PHP |
+---------------------------------------+

As you can see, the query has indeed matched the three rows that contain the string “nice”. One important thing to note is that the minimum size of word that MySQL will index is, by default, four characters – anything smaller than that is rarely worthwhile.

Now consider this second query:

SELECT * FROM opinions WHERE MATCH(Opinion) AGAINST ('nice language');

What do you think it will output? If you thought it would output just one row, you would be wrong – MySQL uses OR by default when matching words, meaning it will return any row that matches “nice” or matches “language” – this is where boolean mode searches come in.

Boolean mode searching has long been popular with internet search engines – they allow you to proceed words with a + or a – to force it to either be present (+) or not present (-). You can switch to boolean mode searching using FULLTEXT indexes by adding “IN BOOLEAN MODE” to the query, like this:

SELECT * FROM opinions WHERE MATCH(Opinion) AGAINST ('nice -language' IN BOOLEAN MODE);

Note that the “IN BOOLEAN MODE” modifier comes inside the AGAINST brackets. This time the query returns just one result – “Spain is a nice country to visit”. This is because “-language” means that MySQL will not return any rows that match “language”, even if they match “nice”. Searching in MySQL has a number of other ways it can be used – for example, putting double quotes around groups of words allow phrase searching. That is, to match precisely “nice language”, we would use the following query:

SELECT * FROM opinions WHERE Match(Opinion) AGAINST ('"nice language"' IN BOOLEAN MODE);

That matches rows that have “nice language” just like that – no words in between, not one or the other. As such, only “PHP is a very nice language” is matched.

There are other boolean operators, although they get more complicated. For example, > and < mark that the following word has a higher or lower relevance respectively than other words. The tilde symbol, ~, means that the following word should contribute negatively to the relevance of the row – this is not the same as <, which marks a word as being less relevant but still relevant nonetheless, and -, which excludes a word altogether. The asterisk symbol, *, allows wildcard matching. Finally, you can use parentheses, ( and ), to group words into subexpressions.

So, all of these examples are possible boolean queries: 

+nice +language

Match both nice and language

+nice -language

Match nice but not language

+nice ~language

Match nice, but mark down as less relevant rows that contain language

+nice*

Match nice, nicely, nicety, nice language, etc

“nice language”

Match the exact term “nice language”

+nice +(language country)

Match either “nice language” or “nice country”

+nice +(>language <country)

Match either “nice language” or “nice country”, with rows matching “nice language” being considered more relevant

Before we take a look at how these effect queries in practical use, I’d first like to introduce the topic of relevance. Result relevant, often known as match quality or score, is a number returned by MySQL that allows you to rank how much of a query a row matched. To get relevance from a query, simply copy the full-text WHERE clause into the fields you want to select, like this:

SELECT Opinion, MATCH(Opinion) AGAINST('nice language' IN BOOLEAN MODE) AS Score FROM opinions WHERE MATCH(Opinion) AGAINST ('nice language' IN BOOLEAN MODE);

Do not worry about using MATCH() twice – the two matches are identical, and the MySQL optimizer will pick up on this and only execute it once. However, it does give us a new field, Score, to play with. So, the query above says “Select all opinions and their relevance where the row has either "nice” or “language”. Here is what the above query outputs:

+---------------------------------------+-------+
| Opinion                               | Score |
+---------------------------------------+-------+
| PHP is a very nice language           |     2 |
| Spain is a nice country to visit      |     1 |
| Perl is not as nice a language as PHP |     2 |
+---------------------------------------+-------+

Naturally it would be best to sort by Score descending to get the matches in order of most relevant first, however note that the Score of the two fields with “nice” and language in is 2, whereas the field with just “nice” in is 1. Now we get to play with the extended boolean mode operators – for example, if we wanted to match “nice language” or “nice country”, we could use this query:

SELECT Opinion, MATCH(Opinion) AGAINST('+nice +(language country)' IN BOOLEAN MODE) AS Score FROM opinions WHERE MATCH(Opinion) AGAINST ('+nice +(language country)' IN BOOLEAN MODE);

The pluses are in there to make sure that both nice and either language or country are included in the result. This time Score is 2 for all three fields, as all three match nice and all three have either language or country. We can give more relevancy information to MySQL by saying that country is more important to match than language by using >, like this:

SELECT Opinion, MATCH(Opinion) AGAINST('+nice +(language >country)' IN BOOLEAN MODE) AS Score FROM opinions WHERE MATCH(Opinion) AGAINST ('+nice +(language >country)' IN BOOLEAN MODE);

This time we get 1 for the two “nice language” matches and 1.25 for “nice country” – MySQL has increased the score for “country” by 0.25. We can push this further by decreasing the score for “language” at the same time, like this:

SELECT Opinion, MATCH(Opinion) AGAINST('+nice +(<language >country)' IN BOOLEAN MODE) AS Score FROM opinions WHERE MATCH(Opinion) AGAINST ('+nice +(<language >country)' IN BOOLEAN MODE);

The output for that query is shown below – as you can see, the nice language matches have moved down another notch.

+---------------------------------------+------------------+
| Opinion                               |            Score |
+---------------------------------------+------------------+
| PHP is a very nice language           | 0.83333337306976 |
| Spain is a nice country to visit      |             1.25 |
| Perl is not as nice a language as PHP | 0.83333337306976 |
+---------------------------------------+------------------+

The exact numbers in the score are usually irrelevant – it is the ranking that counts. Therefore, we could have achieved the same effect by using the negation operator, ~, which would subtract points from the word language if we used this query:

SELECT Opinion, MATCH(Opinion) AGAINST('+nice ~language)' IN BOOLEAN MODE) AS Score FROM opinions WHERE MATCH(Opinion) AGAINST ('+nice ~language)' IN BOOLEAN MODE);

This time MySQL will match all rows that contain nice or language, but will “penalise” rows that contain language. This is helpful for marking known irrelevant words without specifically removing rows that contain them. 

http://intomysql.blogspot.com/2010/12/full-text-search-engine.html 에서 참조

 전문 검색 엔진(Full text search engine)의 검색 방식 및 인덱싱

검색 방식

  • 자연어 검색
    자 연어 검색이란, 문자 그대로 검색어를 일반적으로 인간이 사용하는 문장이나 절로(자연스러운 문장의 구문) 가정하여 그대로 Matching해서 검색을 실행하는 방법을 의미한다.  별도의 연산자를 사용할 수 없으며, StopWord 가 적용되며,  50% 이상의 레코드에 존재하는 단어는 일반적인 단어로 간주하여 검색에서 배제한다. 또한, 검색 결과 Match율은 Percentage로 표시된다. MySQL에서는 별도의 옵션을 제공하지 않으면, 자연어 검색을 실행하게 된다.
    (MySQL Built-in 전문 검색엔진을 포함한 대부분의 전문 검색 도구가 지원한다.)
  • Boolean 검색
    자 연어와 달리 검색어의 단어 단위로 특별한 예약어 (+-* 등)를 사용하여 검색 방법을 지정할 수 있다. 검색 방식의 이름에서도 알 수 있듯이, 일부 일치(몇 Percentage 일치)와 같은 개념은 존재하지 않으며, Match율은 순수하게 0% 아니면, 100% 일치로 표기된다. 또한 검색 결과도 100% 일치건만 추출된다.
    (MySQL Built-in 전문 검색엔진을 포함한 대부분의 전문 검색 도구가 지원한다.)

인덱싱 방식

  • 구분자 또는 불용어 (Delimiter, StopWord)
    Full Text search에서는 전문 (Text)의 내용의 빠른 검색을 위해서 Index Building이 중요한데, 전문의 내용을 기 정의된 StopWord 목록을 이용하여 Parsing(분리)을 해서 결과 단어들의 목록을 인덱스로 생성해 두고 검색에 이용하는 방법을 말한다.  일반적으로 구분자는 공백이나 쉼표 또는 한국어의 조사 등을 구분자로 많이 사용하게 되며, 특정 시스템별로 아주 자주 사용되는 단어들도 검색의 의미가 없기 때문에 구분자로 등록하는 경우도 많다. (예를 들어 MySQL 홈페이지에서 MySQL 이라는 단어는 검색의 의미가 없기 때문에 구분자로 등록하는 것이 효율적일 수 있다) MySQL의 Built-in 전문 검색 엔진 (FullText search)은 이 방식만으로 인덱싱을 할 수 있다.
  • N-Gram
    지 정된 구분자로 전 세계 모든 언어에서 단어를 구분해 낸다는 것은 쉽지 않을 것이다. 이러한 부분을 보완하기 위해서 지정된 규칙이 없는 전문도 분석 및 검색을 가능하도록 하는 방법이 N-Gram이라는 방식이다. N-Gram이란, 전문을 무조건적으로 몇 글자씩 잘라서 Indexing을 하는 방법이다. 구분자에 의한 방법보다는 Indexing이 복잡하고, 만들어진 Index의 Size도 상당히 큰 편이다.  (Tritonn이나 Sphinx는 다른 인덱싱 방법들도 제공하지만 이 방법이 주로 사용된다.)
  • 형태소 분석
    국 가별 언어에 대해서 각 문장 및 단어들의 문법 또는 의미적인 단위(형태소)로 쪼개어서 구분하고, 구문의 실제 의미를 인식할 수 있는 수준까지 분석하는 방법으로 주로 번역 시스템이나 검색 엔진들이 사용하는 방법이다. 이러한 방식은 상당히 복잡하기 때문에 일반적인 DBMS의 전문 검색 엔진에서는 쉽게 사용되진 않는 편이다. (Tritonn에서는 MeCab(일본어 형태소 분석기) 과 같은 형태소 분석기 라이브러리를 사용하여 적용할 수도 있다.)

Online / Offline 인덱싱

  • Online 인덱싱
    Full Text Search Engine 방식에 따라서, 대상 테이블에 데이터 레코드가 등록, 변경, 삭제되면서, Full Text Search Index에도 실시간으로 변경 분만 적용되는 형태를 의미한다. (MySQL Builtin Engine과 Tritonn Search Engine이 이와 같은 형태로 구현되어 있음)
  • Offline 인덱싱
    대 상 테이블에 데이터 레코드가 등록, 변경, 삭제와는 무관하게 Full Text Search Index가 관리되면서, 실시간으로 Full Text Index에 반영되지 않고, 일정 주기로 수동 Batch 형태로 Full Text Index Building 작업을 해 주어야 하는 형태를 의미한다. (현재, Sphinx Engine이나 Lucene과 같은 소프트웨어가 이와 같은 형태로 구현되어 있음

http://intomysql.blogspot.com/2010/12/full-text-search-engine.html 에서 참조

 전문 검색 엔진(Full text search engine)의 검색 방식 및 인덱싱

검색 방식

  • 자연어 검색
    자 연어 검색이란, 문자 그대로 검색어를 일반적으로 인간이 사용하는 문장이나 절로(자연스러운 문장의 구문) 가정하여 그대로 Matching해서 검색을 실행하는 방법을 의미한다.  별도의 연산자를 사용할 수 없으며, StopWord 가 적용되며,  50% 이상의 레코드에 존재하는 단어는 일반적인 단어로 간주하여 검색에서 배제한다. 또한, 검색 결과 Match율은 Percentage로 표시된다. MySQL에서는 별도의 옵션을 제공하지 않으면, 자연어 검색을 실행하게 된다.
    (MySQL Built-in 전문 검색엔진을 포함한 대부분의 전문 검색 도구가 지원한다.)
  • Boolean 검색
    자 연어와 달리 검색어의 단어 단위로 특별한 예약어 (+-* 등)를 사용하여 검색 방법을 지정할 수 있다. 검색 방식의 이름에서도 알 수 있듯이, 일부 일치(몇 Percentage 일치)와 같은 개념은 존재하지 않으며, Match율은 순수하게 0% 아니면, 100% 일치로 표기된다. 또한 검색 결과도 100% 일치건만 추출된다.
    (MySQL Built-in 전문 검색엔진을 포함한 대부분의 전문 검색 도구가 지원한다.)

인덱싱 방식

  • 구분자 또는 불용어 (Delimiter, StopWord)
    Full Text search에서는 전문 (Text)의 내용의 빠른 검색을 위해서 Index Building이 중요한데, 전문의 내용을 기 정의된 StopWord 목록을 이용하여 Parsing(분리)을 해서 결과 단어들의 목록을 인덱스로 생성해 두고 검색에 이용하는 방법을 말한다.  일반적으로 구분자는 공백이나 쉼표 또는 한국어의 조사 등을 구분자로 많이 사용하게 되며, 특정 시스템별로 아주 자주 사용되는 단어들도 검색의 의미가 없기 때문에 구분자로 등록하는 경우도 많다. (예를 들어 MySQL 홈페이지에서 MySQL 이라는 단어는 검색의 의미가 없기 때문에 구분자로 등록하는 것이 효율적일 수 있다) MySQL의 Built-in 전문 검색 엔진 (FullText search)은 이 방식만으로 인덱싱을 할 수 있다.
  • N-Gram
    지 정된 구분자로 전 세계 모든 언어에서 단어를 구분해 낸다는 것은 쉽지 않을 것이다. 이러한 부분을 보완하기 위해서 지정된 규칙이 없는 전문도 분석 및 검색을 가능하도록 하는 방법이 N-Gram이라는 방식이다. N-Gram이란, 전문을 무조건적으로 몇 글자씩 잘라서 Indexing을 하는 방법이다. 구분자에 의한 방법보다는 Indexing이 복잡하고, 만들어진 Index의 Size도 상당히 큰 편이다.  (Tritonn이나 Sphinx는 다른 인덱싱 방법들도 제공하지만 이 방법이 주로 사용된다.)
  • 형태소 분석
    국 가별 언어에 대해서 각 문장 및 단어들의 문법 또는 의미적인 단위(형태소)로 쪼개어서 구분하고, 구문의 실제 의미를 인식할 수 있는 수준까지 분석하는 방법으로 주로 번역 시스템이나 검색 엔진들이 사용하는 방법이다. 이러한 방식은 상당히 복잡하기 때문에 일반적인 DBMS의 전문 검색 엔진에서는 쉽게 사용되진 않는 편이다. (Tritonn에서는 MeCab(일본어 형태소 분석기) 과 같은 형태소 분석기 라이브러리를 사용하여 적용할 수도 있다.)

Online / Offline 인덱싱

  • Online 인덱싱
    Full Text Search Engine 방식에 따라서, 대상 테이블에 데이터 레코드가 등록, 변경, 삭제되면서, Full Text Search Index에도 실시간으로 변경 분만 적용되는 형태를 의미한다. (MySQL Builtin Engine과 Tritonn Search Engine이 이와 같은 형태로 구현되어 있음)
  • Offline 인덱싱
    대 상 테이블에 데이터 레코드가 등록, 변경, 삭제와는 무관하게 Full Text Search Index가 관리되면서, 실시간으로 Full Text Index에 반영되지 않고, 일정 주기로 수동 Batch 형태로 Full Text Index Building 작업을 해 주어야 하는 형태를 의미한다. (현재, Sphinx Engine이나 Lucene과 같은 소프트웨어가 이와 같은 형태로 구현되어 있음
FullText 의 개념
mysql 메뉴얼을 읽어보면 알 수있듯이
FullText 검색 기능의 개념 자체는 작은 규모가 아닌 큰 규모의 
문자열 집합을(Large collections) 대상으로 빠르게 해당 검색어를 
검색하기 위해서 만들어진 것입니다.
그리고, 이것은 ‘Like’ 문 기능과는 다르고,
‘Where 필드 = 검색문자열’ 문과도 (어떻게 만드느냐에 따라서) 다를 수 있습니다.
큰 규모라고 하면, 웹 검색엔진을(the Internet with a search engine) 
상상해보시면 될겁니다. 구글(Google)의 경우 30억개 이상의 웹페이지를 
대상으로 검색할 수 있다고 사이트에 명시하고 있습니다. DB 개념으로 
이해하자면, 웹페이지와 같은 큰 문자열을 각각 지니고 있는 30억개 
이상의 레코드들을 검색한다는 것입니다.
이런 방대한 자료에서 어떤 문자열 하나를 찾기위해서 ‘Like’ 문을 쓴다고 상상해보세요.
여기에, 전 세계적으로 엄청난 동시 사용자들을 감안한다면, ‘Like’ 문같은 검색은 
쓸 수가 없겠죠. 실제로 구글에서 여러가지 문자열을 가지고 테스트 
해보시면, 절대 Like 검색방식으로 처리가 되고있지 않다는 것을 알 수 
있습니다.
그리고, ‘Where 필드 = 검색문자열’과 같은 것은, FullText 검색기능을 
내부적으로 어느 정도선까지 지원하도록 개발하느냐에 따라서 틀려질 수 
있습니다.
 
단어를 포함하는 모든 자료를 가져올지, 아니면 단어들중 의미 
있다고 판단되는 단어만을 포함하는 자료를 가져올지 등등… 구글은 
전자의 형태로 개발된 것 같습니다.
당장, ‘a’, ‘are’, ‘this’라는 단어로 검색해보면 엄청난 수의 검색결과를 볼 수 있습니다. MySQL의 FullText 검색은 보통 후자를 택하고 있고,
일종의 자연어 검색(Natural Language Search)을 한다고 밝히고 있습니다.
(단, Boolean Mode도 따로 제공하고 있습니다.)
 
* ‘자연어 검색’에 대해서는 잘 모르지만, 그것과는 별개로, MySQL이 
채택한 검색방식의 핵심을 이해하기 위해서는, 해당부분 MySQL 메뉴얼에서 
자주 쓰인 ‘적절성 값(Revelence Value)’라는 말을 이해해야 될겁니다.
‘적절성 값(Revelence Value)’의 뜻은, 레코드에 포함되어있는 문자열과 
검색하려는 검색어가 얼마나 유사성(Similarity)을 갖고 있는지를 측정한 
것이라고 합니다.(여기에서의 유사성은 단어중 몇글자가 같냐는 식으로 
판단되는 것은 아닙니다.) 그리고, 이 값은 다음과 같은 것들을 계산해서 
나온다고 합니다.
 
레코드안에 들어있는 단어들의 수 
(the number of words in the row) 
그 레코드 안에서 유일한 단어들의 수 
(the number of unique words in that row) 
그 집합 안에서 단어들의  총 갯수 
(the total number of words in the collection) 
특별한 단어를 포함하고 있는 문서들(레코드들)의 수 
(the number of documents (rows) that contain a particular word)
 
‘유사성’이라는 말을 염두에 둔다면, 대충 Revelence Value가 어떻게 계산 
되는지를 유추해볼 수는 있을겁니다. 예를 들어, 검색어로 5개의 단어를 
입력했다면, 5개의 단어들중 각각의 레코드가 몇개의 단어를 포함하고 
있는지, 5개 각각의 단어들이 몇개씩 포함하고 있는지 등에 따라서 
유사성이 틀려질 수 있겠지요. 이 값이 0이면, 유사성이 전혀 없다는 것을 
의미합니다. (Zero relevance means no similarity.)
fulltext 를 이용한 검색 기능 구현 방식은 아래와 같습니다.
 
1. 테이블 생성시에 검색할 필드를 전부 fulltext() 로 잡습니다.
fulltext ( name, subject, contents )
2. 검색 쿼리
select * from table match(name,subject, contents ) against(’“검색단어”’ in boolean mode);
3. 쿼리시 주의점
in boolean mode 선언이 반드시 필요하며, match()의 필드는 fulltext() 에서 선언된 그대로 넣어야합니다.
 
against 에서는 작은따옴표 안에 큰따옴표를 사용해야 온전한 글로 인식되어 검색됩니다.
따옴표가 하나밖에 없다면, or 검색이 되어 원치 않는 결과가 나옵니다
4. 필드 추가시
alter table ‘해당테이블’ add fulltext(‘추가필드’)
FullText 의 개념
mysql 메뉴얼을 읽어보면 알 수있듯이
FullText 검색 기능의 개념 자체는 작은 규모가 아닌 큰 규모의 
문자열 집합을(Large collections) 대상으로 빠르게 해당 검색어를 
검색하기 위해서 만들어진 것입니다.
그리고, 이것은 ‘Like’ 문 기능과는 다르고,
‘Where 필드 = 검색문자열’ 문과도 (어떻게 만드느냐에 따라서) 다를 수 있습니다.
큰 규모라고 하면, 웹 검색엔진을(the Internet with a search engine) 
상상해보시면 될겁니다. 구글(Google)의 경우 30억개 이상의 웹페이지를 
대상으로 검색할 수 있다고 사이트에 명시하고 있습니다. DB 개념으로 
이해하자면, 웹페이지와 같은 큰 문자열을 각각 지니고 있는 30억개 
이상의 레코드들을 검색한다는 것입니다.
이런 방대한 자료에서 어떤 문자열 하나를 찾기위해서 ‘Like’ 문을 쓴다고 상상해보세요.
여기에, 전 세계적으로 엄청난 동시 사용자들을 감안한다면, ‘Like’ 문같은 검색은 
쓸 수가 없겠죠. 실제로 구글에서 여러가지 문자열을 가지고 테스트 
해보시면, 절대 Like 검색방식으로 처리가 되고있지 않다는 것을 알 수 
있습니다.
그리고, ‘Where 필드 = 검색문자열’과 같은 것은, FullText 검색기능을 
내부적으로 어느 정도선까지 지원하도록 개발하느냐에 따라서 틀려질 수 
있습니다.
 
단어를 포함하는 모든 자료를 가져올지, 아니면 단어들중 의미 
있다고 판단되는 단어만을 포함하는 자료를 가져올지 등등… 구글은 
전자의 형태로 개발된 것 같습니다.
당장, ‘a’, ‘are’, ‘this’라는 단어로 검색해보면 엄청난 수의 검색결과를 볼 수 있습니다. MySQL의 FullText 검색은 보통 후자를 택하고 있고,
일종의 자연어 검색(Natural Language Search)을 한다고 밝히고 있습니다.
(단, Boolean Mode도 따로 제공하고 있습니다.)
 
* ‘자연어 검색’에 대해서는 잘 모르지만, 그것과는 별개로, MySQL이 
채택한 검색방식의 핵심을 이해하기 위해서는, 해당부분 MySQL 메뉴얼에서 
자주 쓰인 ‘적절성 값(Revelence Value)’라는 말을 이해해야 될겁니다.
‘적절성 값(Revelence Value)’의 뜻은, 레코드에 포함되어있는 문자열과 
검색하려는 검색어가 얼마나 유사성(Similarity)을 갖고 있는지를 측정한 
것이라고 합니다.(여기에서의 유사성은 단어중 몇글자가 같냐는 식으로 
판단되는 것은 아닙니다.) 그리고, 이 값은 다음과 같은 것들을 계산해서 
나온다고 합니다.
 
레코드안에 들어있는 단어들의 수 
(the number of words in the row) 
그 레코드 안에서 유일한 단어들의 수 
(the number of unique words in that row) 
그 집합 안에서 단어들의  총 갯수 
(the total number of words in the collection) 
특별한 단어를 포함하고 있는 문서들(레코드들)의 수 
(the number of documents (rows) that contain a particular word)
 
‘유사성’이라는 말을 염두에 둔다면, 대충 Revelence Value가 어떻게 계산 
되는지를 유추해볼 수는 있을겁니다. 예를 들어, 검색어로 5개의 단어를 
입력했다면, 5개의 단어들중 각각의 레코드가 몇개의 단어를 포함하고 
있는지, 5개 각각의 단어들이 몇개씩 포함하고 있는지 등에 따라서 
유사성이 틀려질 수 있겠지요. 이 값이 0이면, 유사성이 전혀 없다는 것을 
의미합니다. (Zero relevance means no similarity.)
fulltext 를 이용한 검색 기능 구현 방식은 아래와 같습니다.
 
1. 테이블 생성시에 검색할 필드를 전부 fulltext() 로 잡습니다.
fulltext ( name, subject, contents )
2. 검색 쿼리
select * from table match(name,subject, contents ) against(’“검색단어”’ in boolean mode);
3. 쿼리시 주의점
in boolean mode 선언이 반드시 필요하며, match()의 필드는 fulltext() 에서 선언된 그대로 넣어야합니다.
 
against 에서는 작은따옴표 안에 큰따옴표를 사용해야 온전한 글로 인식되어 검색됩니다.
따옴표가 하나밖에 없다면, or 검색이 되어 원치 않는 결과가 나옵니다
4. 필드 추가시
alter table ‘해당테이블’ add fulltext(‘추가필드’)

question :

I’m developing a high-volume web application, where part of it is a MySQL database of discussion posts that will need to grow to 20M+ rows, smoothly.

I was originally planning on using MyISAM for the tables (for the built-in fulltext search capabilities), but the thought of the entire table being locked due to a single write operation makes me shutter. Row-level locks make so much more sense (not to mention InnoDB’s other speed advantages when dealing with huge tables). So, for this reason, I’m pretty determined to use InnoDB.

The problem is… InnoDB doesn’t have built-in fulltext search capabilities.

Should I go with a third-party search system? Like Lucene(c++) / Sphinx? Do any of you database ninjas have any suggestions/guidance? LinkedIn’s zoie (based off Lucene) looks like the best option at the moment… having been built around realtime capabilities (which is pretty critical for my application.) I’m a little hesitant to commit yet without some insight…

(FYI: going to be on EC2 with high-memory rigs, using PHP to serve the frontend)

answer1:

I can vouch for MyISAM fulltext being a bad option – even leaving aside the various problems with MyISAM tables in general, I’ve seen the fulltext stuff go off the rails and start corrupting itself and crashing MySQL regularly.

A dedicated search engine is definitely going to be the most flexible option here – store the post data in MySQL/innodb, and then export the text to your search engine. You can set up a periodic full index build/publish pretty easily, and add real-time index updates if you feel the need and want to spend the time.

Lucene and Sphinx are good options, as is Xapian, which is nice and lightweight. If you go the Lucene route don’t assume that Clucene will better, even if you’d prefer not to wrestle with Java, although I’m not really qualified to discuss the pros and cons of either.

answer2:

Along with the general phasing out of MyISAM, InnoDB full-text search (FTS) is finally available in MySQL 5.6.4 release.

From http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html#innodb-fulltext-index:

These indexes are physically represented as entire InnoDB tables, which are acted upon by SQL keywords such as the FULLTEXT clause of the CREATE INDEX statement, the MATCH() … AGAINST syntax in a SELECT statement, and the OPTIMIZE TABLE statement.

While other engines have lots of different features, this one is InnoDB, so it’s native (which means there’s an upgrade path), and that makes it a worthwhile option.

answer:3

You should spend an hour and go through installation and test-drive of Sphinx and Lucene. See if either meets your needs, with respect to data updates.

One of the things that disappointed me about Sphinx is that it doesn’t support incremental inserts very well. That is, it’s very expensive to reindex after an insert, so expensive that their recommended solution is to split your data into older, unchanging rows and newer, volatile rows. So every search your app does would have to search twice: once on the larger index for old rows and also on the smaller index for recent rows. If that doesn’t integrate with your usage patterns, this Sphinx is not a good solution (at least not in its current implementation).

I’d like to point out another possible solution you could consider: Google Custom Search. If you can apply some SEO to your web application, then outsource the indexing and search function to Google, and embed a Google search textfield into your site. It could be the most economical and scalable way to make your site searchable. 

http://www.iamcal.com/publish/articles/php/search/

PHP : Searching with PHP and MySQL
So you have a PHP/MySQL website, and you want to let users search for things. Easy!

$search_term_esc = AddSlashes($search_term);

$sql = “SELECT * FROM Content WHERE content_body LIKE ’%$search_term_esc%’”;
Great. Only not really. If I enter ’%’ or ’_’ as my search term, it’ll match every piece of content you have. And if someone enters ‘e’ then it’ll match every article with an ‘e’ in it. And if someone enters ‘foo bar’ it’ll match the phrase, not the two words. It looks like we have a lot to learn.

Step one is breaking the terms apart. We could do this with a simple explode, but wouldn’t it be cool if we could also allow phrases by accepting quotes? One easy way to achieve this is with a simple three step routine:

Convert whitespace between brackets into something non-whitespacey.
Split on whitespace.
Convert the ‘something’ back to the whitespace it was, for each token.
For the first stage, we can use my favourite feature of regular expressions – the ‘e’ (evaluation) flag.

$terms = preg_replace(“/”(.*?)”/e”, “search_transform_term(’$1’)”, $terms);

function search_transform_term($term){
$term = preg_replace(“/(s)/e”, “’{WHITESPACE-’.ord(’$1’).’}’”, $term);
$term = preg_replace(“/,/”, “{COMMA}”, $term);
return $term;
}
So what happens here exactly? The first preg_replace() call find all pair of brackets and passes their contents to the search_transform_term() function for processing. We then use a further two preg_replace() calls to replace any whitespace with a holder token, and the same with commas. We process commas incase users enter comma seperated terms, instead of space seperated ones.

With this done, we’re ready to split on whitespace and commas. We’ll use preg_split() for this:

$terms = preg_split(“/s+|,/”, $terms);
For each term, we then want to replace the holding tokens back with their original contents.

$term = preg_replace(“/{WHITESPACE-([0-9]+)}/e”, “chr($1)”, $term);
$term = preg_replace(“/{COMMA}/”, “,”, $term);
So now we have a full function for splitting up search terms:

function search_split_terms($terms){

$terms = preg_replace(“/”(.*?)”/e”, “search_transform_term(’$1’)”, $terms);
$terms = preg_split(“/s+|,/”, $terms);

$out = array();

foreach($terms as $term){

$term = preg_replace(“/{WHITESPACE-([0-9]+)}/e”, “chr($1)”, $term);
$term = preg_replace(“/{COMMA}/”, “,”, $term);

$out[] = $term;
}

return $out;
}

function search_transform_term($term){
$term = preg_replace(“/(s)/e”, “’{WHITESPACE-’.ord(’$1’).’}’”, $term);
$term = preg_replace(“/,/”, “{COMMA}”, $term);
return $term;
}
On to stage two – doing a useful search with each term. Instead of using MySQL’s LIKE operator, we’ll use it’s much more powerful friend, RLIKE, which lets us use regular expressions.

MySQL’s regular expressions are quite perl-like, but not entirely. The first thing we need is an escaping function, since some of the characters in the search term might contain a MySQL regular expression meta-character.

function search_escape_rlike($string){
return preg_replace(“/([.[]*^$])/”, ’\$1’, $string);
}
This function inserts a slash before each meta-character that MySQL uses. Next we need to make sure each term matches on word boundaries so that ‘foo’ matches ‘a foo a’ but not ‘a food a’. In MySQL syntax, these are ’[[:<:]]’ and ’[[:>:]]’. We’ll write a function to turn our list of terms into a list of regular expressions.

function search_db_escape_terms($terms){
$out = array();
foreach($terms as $term){
$out[] = ’[[:<:]]’.AddSlashes(search_escape_rlike($term)).’[[:>:]]’;
}
return $out;
}
So now we’re ready to build some SQL. I’m assuming a single content table with a single field to match. You can figure out how to expand it to something more complicated yourself.

$terms = search_split_terms($terms);
$terms_db = search_db_escape_terms($terms);

$parts = array();
foreach($terms_db as $term_db){
$parts[] = “content_body RLIKE ’$term_db’”;
}
$parts = implode(’ AND ’, $parts);

$sql = “SELECT * FROM Content WHERE $parts”;
With this function, the search string ‘foo bar’ generates this SQL:

SELECT * FROM Content WHERE content_body RLIKE ’[[:<:]]foo[[:>:]]’
AND content_body RLIKE ’[[:<:]]bar[[:>:]]’;
You can replace the ‘AND’ with an ‘OR’ if you want to match pages which match any of the terms, rather than all.

Maybe you want to rank the results by how many times the search terms appear in them – that’s what users often expect.

$terms_rx = search_rx_escape_terms($terms);

$rows = array();

$result = mysql_query($sql);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){

$row[score] = 0;

foreach($terms_rx as $term_rx){
$row[score] += preg_match_all(“/$term_rx/i”, $row[content_body], $null);
}

$rows[] = $row;
}

uasort($rows, ‘search_sort_results’);

function search_rx_escape_terms($terms){
$out = array();
foreach($terms as $term){
$out[] = ’b’.preg_quote($term, ’/’).’b’;
}
return $out;
}

function search_sort_results($a, $b){

$ax = $a[score];
$bx = $b[score];

if ($ax == $bx){ return 0; }
return ($ax > $bx) ? -1 : 1;
}
Here we use preg_match_all() to find the number of matches for each term in the content. Our search_rx_escape_terms() turns each of the terms into a preg style regular expression. After stashing each of the matching results in $rows, along with its’ score, we use usort() to sort the results using a custom function.

We have a final touch left – displaying the list of search terms back to the user with the results. For this, we want to show quoted terms and display them in a “pretty” list:

$terms_html = search_html_escape_terms($terms);

function search_html_escape_terms($terms){
$out = array();

foreach($terms as $term){
if (preg_match(“/s|,/”, $term)){
$out[] = ’“’.HtmlSpecialChars($term).’”’;
}else{
$out[] = HtmlSpecialChars($term);
}
}

return $out;
}

function search_pretty_terms($terms_html){

if (count($terms_html) == 1){
return array_pop($terms_html);
}

$last = array_pop($terms_html);

return implode(’, ’, $terms_html).“ and $last”;
}
Understanding the above function is left an an exercise for the reader 😉

So now we have our full suite of search code – let’s bring it all together:

function search_split_terms($terms){

$terms = preg_replace(“/”(.*?)”/e”, “search_transform_term(’$1’)”, $terms);
$terms = preg_split(“/s+|,/”, $terms);

$out = array();

foreach($terms as $term){

$term = preg_replace(“/{WHITESPACE-([0-9]+)}/e”, “chr($1)”, $term);
$term = preg_replace(“/{COMMA}/”, “,”, $term);

$out[] = $term;
}

return $out;
}

function search_transform_term($term){
$term = preg_replace(“/(s)/e”, “’{WHITESPACE-’.ord(’$1’).’}’”, $term);
$term = preg_replace(“/,/”, “{COMMA}”, $term);
return $term;
}

function search_escape_rlike($string){
return preg_replace(“/([.[]*^$])/”, ’\$1’, $string);
}

function search_db_escape_terms($terms){
$out = array();
foreach($terms as $term){
$out[] = ’[[:<:]]’.AddSlashes(search_escape_rlike($term)).’[[:>:]]’;
}
return $out;
}

function search_perform($terms){

$terms = search_split_terms($terms);
$terms_db = search_db_escape_terms($terms);
$terms_rx = search_rx_escape_terms($terms);

$parts = array();
foreach($terms_db as $term_db){
$parts[] = “content_body RLIKE ’$term_db’”;
}
$parts = implode(’ AND ’, $parts);

$sql = “SELECT * FROM Content WHERE $parts”;

$rows = array();

$result = mysql_query($sql);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){

$row[score] = 0;

foreach($terms_rx as $term_rx){
$row[score] += preg_match_all(“/$term_rx/i”, $row[content_body], $null);
}

$rows[] = $row;
}

uasort($rows, ‘search_sort_results’);

return $rows;
}

function search_rx_escape_terms($terms){
$out = array();
foreach($terms as $term){
$out[] = ’b’.preg_quote($term, ’/’).’b’;
}
return $out;
}

function search_sort_results($a, $b){

$ax = $a[score];
$bx = $b[score];

if ($ax == $bx){ return 0; }
return ($ax > $bx) ? -1 : 1;
}

function search_html_escape_terms($terms){
$out = array();

foreach($terms as $term){
if (preg_match(“/s|,/”, $term)){
$out[] = ’“’.HtmlSpecialChars($term).’”’;
}else{
$out[] = HtmlSpecialChars($term);
}
}

return $out;
}

function search_pretty_terms($terms_html){

if (count($terms_html) == 1){
return array_pop($terms_html);
}

$last = array_pop($terms_html);

return implode(’, ’, $terms_html).“ and $last”;
}

#
# do the search here…
#

$results = search_perform($HTTP_GET_VARS[q]);
$term_list = search_pretty_terms(search_html_escape_terms(search_split_terms($HTTP_GET_VARS[q])));

#
# of course, we’re using smarty 😉
#

$smarty->assign(‘term_list’, $term_list);

if (count($results)){

$smarty->assign(‘results’, $results);
$smarty->display(‘search_results.txt’);
}else{

$smarty->display(‘search_noresults.txt’);
}

And there we go. Not quite as easy as our very first example, but alot more useful for your users.

If you’re not totally sick of searching yet, then you might like to add some search term highlighting.

Note: The code in this article is designed for small sized tables. A medium to large dataset will make for very heavy processing – regular expressions are not CPU cheap and can’t be indexed. For large scale websites, you need a proper enterprise-level solution. Xapian is a good place to start. For medium sized tables, MySQL’s own FULLTEXT indexes can do some fancy things. The downsides are that you’ll need to use MySQL 4.1 if you have UTF-8 data and your tables will have to be MyISAM, which has it’s own set of issues.