1. 단어 단위 Fulltext Search

Fulltext Index 생성하기

방법 1

create fulltext index idx_ft_profile_name_and_name_and_description on profiles(profile_name, firstname_lastname, description);

방법 2

ALTER TABLE profiles ADD FULLTEXT INDEX idx_ft_profile_name_and_name_and_description(profile_name, firstname_lastname, description);

단어로 Full text search 실행하기

-- 'Elsa'이라는 단어로 FULL TEXT SEARCH 실행 (NATURAL MODE)
SELECT * FROM profiles WHERE MATCH (profile_name, firstname_lastname, description) AGAINST ('Elsa' IN NATURAL LANGUAGE MODE);

2. N-gram으로 Fulltext Seach

ngram parser를 이용해서 Index 다시 설정하기

-- 1. DROP INDEX
DROP INDEX idx_ft_profile_name_and_name_and_description ON users;

-- 2. CREATE NGRAM INDEX
ALTER TABLE users ADD FULLTEXT INDEX idx_ft_profilename_and_name_and_description(profile_name, firstname_lastname, description) WITH PARSER NGRAM;

Natural Language Mode

: 자연어 검색

• 검색 문자열을 단어 단위로 분리한 후, 해당 단어 중 하나라도 포함되는 행을 찾는다.

ex) SELECT * FROM “테이블명" WHERE MATCH (“검색컬럼명") AGAINST (‘맛집' IN NATURAL LANGUAGE MODE);

💡 자연어란?

• 일반 사회에서 자연히 발생하여 사람이 의사소통에 사용하는 언어
• 인공어와 대비되는 개념 (ex: 프로그래밍 언어)

Boolean Mode

: 불린 모드 검색

• 검색 문자열을 단어 단위로 분리한 후, 해당 단어가 포함되는 행을 찾는 규칙을 추가적으로 적용하여 해당 규칙에 매칭되는 행을 찾는다.
• 검색의 정확도에 따라 결과가 정렬되지 않는다.
• 구문 검색이 가능하다.
• 필수(+), 예외(-), 부분(*), 구문(“ ") 연산자를 사용할 수 있다

ex) SELECT FROM “테이블명" WHERE MATCH (“검색컬럼명") AGAINST (‘+대구+닭+맛집' IN BOOLEAN MODE);

Query Extension Search

: 쿼리 확장 검색

• 2단계에 걸쳐서 검색을 수행한다.

• 1단계 : 자연어 검색 수행

• 2단계 : 첫 번째 검색의 결과에 매칭된 행을 기반으로 검색 문자열을 재구성하여 두 번째 검색을 수행

이는 1단계 검색에서 사용한 단어와 연관성이 있는 단어가 1단계 검색에 매칭된 결과에 나타난다는 가정을 전제로 한다.

검색하기

• Natural Language Mode

  SELECT * FROM users WHERE MATCH (profile_name, firstname_lastname, description) AGAINST ('Elsa' IN NATURAL LANGUAGE MODE);

• Natural Language Mode + Score

  SELECT *, MATCH (profile_name, firstname_lastname, description) AGAINST (:keyword IN NATURAL LANGUAGE MODE) as score
   FROM users WHERE MATCH(profile_name, firstname_lastname, description) AGAINST(:keyword);

• Boolean Mode

  SELECT * FROM users WHERE MATCH (profile_name, firstname_lastname, description) AGAINST (:keyword IN BOOLEAN MODE);

• Boolean Mode + case문 + order by

  SELECT sid, profile_name, business_account_tf, firstname_lastname, profile_id, n_posts, following, followers, description, url
    FROM users u
    WHERE MATCH (profile_name, firstname_lastname, description) AGAINST (:keyword IN BOOLEAN MODE)
    ORDER BY CASE
        WHEN u.firstname_lastname = :original then 1
        WHEN u.profile_name = :original then 2
        WHEN u.description = :original then 3
        ELSE 4 END
    LIMIT 20;