[SQL] FULLTEXT INDEX

FULLTEXT INDEX

- 텍스트 검색을 빠르게 수행하기 위해 사용되는 특수 인덱스 

- 일반적으로 긴 텍스트에서 특정 단어를 검색할 때 성능이 크게 향상 

- 자연어(Natural Language) 검색 가능

---

Natural Language Search

LIKE: 단순 검색

FULLTEXT INDEX: 연관도 반영한 검색 

CREATE FULLTEXT INDEX idx_overview ON movies (overview); 
-- movies 테이블의 overview 칼럼에 대해 FULLTEXT INDEX를 생성('idx_overview') 
-- overview 전체 내용을 인덱싱


SELECT title, overview FROM movies 
  WHERE MATCH(overview) AGAINST ('the food');
-- 'the food'가 포함된 모든 영화 검색 
-- 연관성(relevance score) 높은 순서대로 반환

 

MySQL의 자연어 텍스트 검색 

https://dev.mysql.com/doc/refman/8.4/en/fulltext-natural-language.html

---

Boolean Mode Search

ex) overview 칼럼에 특정 단어가 있는지 여부

SELECT title, overview FROM movies 
  WHERE MATCH(overview) AGAINST ('+food snack -drink' IN BOOLEAN MODE);
-- overview에 'food'가 있고 'drink'는 없는 영화를 검색 
-- snack은 필수는 아니지만 있으면 좋음 

SELECT title, overview FROM movies 
  WHERE MATCH(overview) AGAINST ('+food >asia <europe' IN BOOLEAN MODE);
-- food는 반드시 포함
-- contribution: asia는 있으면 좋음 / europe는 없으면 좋음

 

 

MySQL의 Boolean FullText Search 

https://dev.mysql.com/doc/refman/8.4/en/fulltext-boolean.html

---

 

Query Expansion

- 사용자가 입력한 검색어를 자동으로 확장하여 더 많은 결과를 반환 

- 검색어가 너무 짧거나 모호한 경우 

 

SELECT title, overview FROM movies 
  WHERE MATCH(overview) AGAINST ('kimchi' WITH QUERY EXPANSION);

동작 방식 

1) 기본 검색: 데이터베이스에서 'kimchi'라는 단어를 포함하는 overview를 검색

2) 확장 검색: 'kimchi' 뿐만 아니라 'Korea', 'cabbage' 와 같은 연관 단어로 다시 검색 

 

MySQL의 Query Expansion

https://dev.mysql.com/doc/refman/8.4/en/fulltext-query-expansion.html